Remove the leading Zero's and reduce length

Question

Hi

 

I have a table with a column which contains data like:

 

0000123456_PF_20110531_P_F

 

Is it possible in a select statement to remove the leading 4 Zero's and the '_PF_20110531_P_F' so i am just left with 123456?

Some of the data will contain 4 Zero's at the begining some will have 3 like so:

0001234567_PF_20110531_P_F

 

Many thanks

Paul

Answer 1

im assuming this '_PF_20110531_P_F' is a fixed length part of the field, if so the following expression should work, just replace that ID field with whatever your's is called

=replace(left(Fields!ID.Value,4),"0","")  &mid(left(Fields!ID.Value,len(Fields!ID.Value)-16),5)

Answer 2

Hi Nehemiah Willis

 

Many thanks for your reply.  However i am getting a few errors when trying to use this.

 

Incorrect syntax near the keyword 'left'

An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

 

i assume that i use this as part of the select statement?   My table name is 'documents' and field name is 'name'.

 

Any ideas?

 

Many thanks

Paul

Answer 3

no this would be an expression used in the report in place of where ever you need to use it at.  But this kind of logic can also implemented in the query also.

Answer 4

here is an example doing it in a tsql query:

 

create table #tmp(col varchar(50));

insert into #tmp values('0009123456_PF_20110531_P_F');

insert into #tmp values('0000123456_PF_20110531_P_F');

select (replace(left(col,4),'0','')  + substring(left(col,len(col)-16),5,30))  from #tmp;

Answer 5

Here is how you can 'trim' leading zeroes in T-SQL:

DECLARE @t TABLE( 
     ColZeroes VARCHAR(30) 
     ) 

INSERT INTO @t 
SELECT '0000123456_PF_20110531_P_F' 
UNION ALL 
SELECT '0001234567_PF_20110531_P_F' 
UNION ALL 
SELECT 'This does not have' 

SELECT ColZeroes, 
  CASE 
   WHEN PATINDEX('%[^0]%',ColZeroes) > 0 THEN SUBSTRING(ColZeroes,PATINDEX('%[^0]%',ColZeroes),LEN(ColZeroes)) 
   ELSE ColZeroes 
  END 
FROM @t

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 6

Hi,

Modification in Naomi post to only achieve the number between leading 0's and _PF.

CASE
   WHEN PATINDEX('%[^0]%',ColZeroes) > 0 AND PATINDEX('%[_]%',ColZeroes) > 0  THEN SUBSTRING(ColZeroes,PATINDEX('%[^0]%',ColZeroes),PATINDEX('%[_PF]%',ColZeroes) - PATINDEX('%[^0]%',ColZeroes))
   ELSE ColZeroes
END

Hope its helpful....

---

Pavan Kokkula Tata Consultancy Services.

Answer 7

HI,

 

     You can do that in the SQL itself select  substring(Column, patindex('%[^0]%',Column), 10)

 

Regards,

Bharath R S

Answer 8

Many thanks everyone for your help.

 

i have a similar question.  I have data in a table like so:

 

123456 - test

598762 - test2

78952 - test3

95635 - test4

 

What i need is the numbers removing so i am left with test, test2 etc. As you can see some of the numbers are 6 long some are 5.

 

Any ideas?

 

Many thanks

Paul

Answer 9

If you always have - before the rest of the string, then

select substring(Column, charindex(' - ', Column) + 3, len(Column)) 

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 10

Hi

 

Many thanks for this - worked a treat.

I have tried to find a good explanation on using the 'Substring'but have failed.

 

Would it be possible for you to expain how this works?  Would like to teach myself not just ask for the answers!!

 

Many thanks for all your help.

Paul

Answer 11

SUBSTRING function takes a portion of the string started from the N position (second argument) and ended with the Mth position (specified in the last, 3rd argument).

So, suppose we have a string 

'ABC'

If we specify 2 for the second argument and length of the string as the last argument (it will mean we want to get the whole remainder of the string and we don't know exactly how many characters, so we'll specify a number which will definitely be enough, as if the last argument is greater than the length of the remainder, the substring function takes all remainder):

declare @str varchar(100)
set @str = 'ABC'
select @str, SUBSTRING(@str,2, len('ABC'))

Now, suppose we have a string like 'A - BC' and we know that this string ALWAYS have ' - ' in it. But we don't know ' - ' position.

So, the first thing is we need to find the position of the ' - ' and since we don't want to include ' - ' in our result, we add 3 (the length of ' - ') to the position found.

So,

declare @str varchar(100)
set @str = 'A - BC'
select @str, SUBSTRING(@str,CHARINDEX(' - ', @str) + 3, len('ABC'))

There are certain gotchas to look for. Say, the length expression (last argument) must be a positive number.

See the rest in BOL SUBSTRING()

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog