Extract a specific part of string. 1

[BTrees]

Hi All,

I have IDs of following structure.

6000000107-1-0000000160-00000074281567
500004272696-1-0000000275-00000073969605
4500006358-1-0000000465-00000073971047
830452869878-1-0000002506-00000074349358

I need to extract digits from id from middle part, For example,

for 6000000107-1-0000000160-00000074281567 (result should be 160)
for 500004272696-1-0000000275-00000073969605 (result should be 275)
for 4500006358-1-0000000465-00000073971047 (result should be 465)
for 830452870371-1-0000002522-00000074349360 (result should be 2522)
for 830452869878-1-0000002506-00000074349358 (result should be 2506)
i.e LAST NON-ZERO DIDGITS OF MILLDE PART
(Note: length of first part may be varied as shown in sample)

Any help please?
Thanks

 

[fredericofonseca]

this may give you what you need

select t.*
     , convert(bigint, substring(t1.sub, 1, charindex('-', t1.sub) - 1))
from (values ('6000000107-1-0000000160-00000074281567')
           , ('500004272696-1-0000000275-00000073969605')
           , ('4500006358-1-0000000465-00000073971047')
           , ('830452870371-1-0000002522-00000074349360')
           , ('830452869878-1-0000002506-00000074349358')
           ) t (src)
outer apply (select substring(t.src, charindex('-', t.src) + 3, 100) as sub) t1

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[gmmastros]

If your strings are ALWAYS 4 parts (ex. 4 separate numbers with dashes between), then you can use the ParseName function to do this. ParseName separates on dot, not dash, so you will need to replace those first.

Ex:

Select ParseName(Replace('830452869878-1-0000002506-00000074349358', '-','.'), 2)

If you need this as an integer...

Select Convert(bigint, ParseName(Replace('830452869878-1-0000002506-00000074349358', '-','.'), 2))

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[BTrees]

Thanks gmmastros, it works. I want to use this as varchar using your code
Select ParseName(Replace('830452869878-1-0000002506-00000074349358', '-','.'), 2)
It gives me leading zeros. I only want nonzero value, i.e '2506'. Can you please help. Thanks

 

[gmmastros]

You can use the 2nd code sample I posted, that converts to BigInt. If you want this as a varchar, there are several ways you can do it. You can convert to bigint, then back to varchar, you you can use this replace method:

Declare @Data VarChar(100);

Set @Data = '0000000160';

Select Replace(LTrim(Replace(@Data, '0', ' ')), ' ', '0')

Basically, replace zero with space, remove the leading spaces, then replace the remaining spaces with 0's. This method only works if there will not be any spaces in your data because characters that should be a space will end up being converted to a 0 character.

Select Replace(LTrim(Replace(ParseName(Replace('6000000107-1-0000000160-00000074281567', '-','.'), 2), '0', ' ')), ' ', '0')

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[BTrees]

It works, thanks Gamm!!!