Sub String from String - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Sub String from String

Author

Topic

vjs2445
Starting Member

16 Posts

Posted - 2011-10-05 : 13:50:31

I have following kind of pattern string and need to only get the portion of string between &

ABCD&T12345&ABCDEF

So my output should be T12345.

Thanks for help.

Regards,

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-10-05 : 14:01:13

SELECT SUBSTRING(myString,CHARINDEX('&',myString)+1,CHARINDEX('&',myString,CHARINDEX('&',myString)+1)-CHARINDEX('&',myString)-1)

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-10-05 : 14:04:21

And if your string always is as above (2 '&'s somewhere in the middle)
declare @str varchar(50) ='ABCD&T12345&ABCDEF'
select parsename(REPLACE(@str,'&','.'),2)

Jim

Everyday I learn something that somebody else already knew

vjs2445
Starting Member

16 Posts

Posted - 2011-10-05 : 14:27:56

Hi jimf and robvolk.

Thanks for your help and code.

Yes I am always going to have 2 & and need to extract the strings between those two &(s).

Regards,

vjs2445
Starting Member

16 Posts

Posted - 2011-10-05 : 14:33:58

Hi jimf,

Your code works fine.

I also see one more issue in which I have some records doesn't have "&" and now it display null.

Is there anyway I can only use this code where "&" exists and then display other records without "&" as the original value.

Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-10-05 : 14:41:01

SELECT CASE WHEN @str LIKE '%&%' THEN parsename(REPLACE(@str,'&','.'),2) ELSE @str END

vjs2445
Starting Member

16 Posts

Posted - 2011-10-05 : 14:45:40

Thanks Rob.

U the Man......=)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources