Returning specific parts of a string

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 2005 Forums

Transact-SQL (2005)

Returning specific parts of a string

Author

Topic

andros30
Yak Posting Veteran

80 Posts

Posted - 2008-04-24 : 18:09:38

I have a field that contains text strings that always follow this syntax "ABC:DE:comment follow". I want to return a new field with only whatever is between the two : (in this case DE but it could be more). It would be perfect if all the field follow that syntax, however, I have variations where users put spaces either before or after the :. Can the spaces be ignored?

Thank you for any help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-24 : 18:33:02

Try this:


DECLARE @s varchar(50)

SET @s = Beginning     :Middle: End'

SELECT SUBSTRING(@s, CHARINDEX (':', @s) + 1, CHARINDEX(':', SUBSTRING(@s, CHARINDEX (':', @s) + 1, DATALENGTH(@s))) - 1)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-25 : 00:09:33

quote:
Originally posted by andros30

I have a field that contains text strings that always follow this syntax "ABC:DE:comment follow". I want to return a new field with only whatever is between the two : (in this case DE but it could be more). It would be perfect if all the field follow that syntax, however, I have variations where users put spaces either before or after the :. Can the spaces be ignored?

Thank you for any help.

If its always following this syntax text1:text2:text3

then you can use this also:-


SELECT LTRIM(RTRIM(REPLACE(PARSENAME(REPLACE(REPLACE(@s,'.','|'),':','.'),2),'|','.')))

You can further simplify this to

SELECT LTRIM(RTRIM(PARSENAME(REPLACE(@s,':','.'),2)))

if you dont have any '.' characters appearing in your string.

andros30
Yak Posting Veteran

80 Posts

Posted - 2008-04-25 : 13:11:53

quote:
Originally posted by tkizer

Try this:

DECLARE @s varchar(50)

SET @s = Beginning :Middle: End'

SELECT SUBSTRING(@s, CHARINDEX (':', @s) + 1, CHARINDEX(':', SUBSTRING(@s, CHARINDEX (':', @s) + 1, DATALENGTH(@s))) - 1)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Thank you. This works. To follow up, can I build on this substring if I have something like Text1:Text2:Text3:Comment and I want to retrieve Text3 after getting Text2?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-04-25 : 14:04:12

you have to parse the original string again, no?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources