# 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

Tuesday, May 31, 2011 6:13 PM

### Answers

• 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)

• Marked as answer by Wednesday, June 8, 2011 1:17 PM
Tuesday, May 31, 2011 8:48 PM
• 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

• Proposed as answer by Wednesday, June 1, 2011 4:59 AM
• Marked as answer by Wednesday, June 8, 2011 1:16 PM
Tuesday, May 31, 2011 11:51 PM
• 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.
• Marked as answer by Wednesday, June 8, 2011 1:16 PM
Wednesday, June 1, 2011 4:59 AM
• HI,

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

Regards,

Bharath R S

• Marked as answer by Wednesday, June 8, 2011 1:16 PM
Wednesday, June 1, 2011 6:13 AM
• 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
• Marked as answer by Friday, June 10, 2011 8:09 AM
Thursday, June 9, 2011 3:42 AM

### All replies

• 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)

• Marked as answer by Wednesday, June 8, 2011 1:17 PM
Tuesday, May 31, 2011 8:48 PM
• 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

Tuesday, May 31, 2011 10:44 PM
• 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.
Tuesday, May 31, 2011 11:11 PM
• 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;

Tuesday, May 31, 2011 11:25 PM
• 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

• Proposed as answer by Wednesday, June 1, 2011 4:59 AM
• Marked as answer by Wednesday, June 8, 2011 1:16 PM
Tuesday, May 31, 2011 11:51 PM
• 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.
• Marked as answer by Wednesday, June 8, 2011 1:16 PM
Wednesday, June 1, 2011 4:59 AM
• HI,

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

Regards,

Bharath R S

• Marked as answer by Wednesday, June 8, 2011 1:16 PM
Wednesday, June 1, 2011 6:13 AM
• 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

Wednesday, June 8, 2011 10:28 PM
• 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
• Marked as answer by Friday, June 10, 2011 8:09 AM
Thursday, June 9, 2011 3:42 AM
• 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

Friday, June 10, 2011 8:13 AM
• 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
Friday, June 10, 2011 11:57 AM