find in string and return part of 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

General SQL Server Forums

New to SQL Server Programming

find in string and return part of string

Author

Topic

nobby
Yak Posting Veteran

58 Posts

Posted - 2007-03-21 : 21:23:01

I am trying to find a way to find a certian character in a string and then select everything after that character.

for example i would look for the position of the underscore and then need to return everthing after it so in this case

yes_no

i would return no

nobby
Yak Posting Veteran

58 Posts

Posted - 2007-03-21 : 21:37:35

i have managed to work out to use this

SUBSTRING(MyField, 4, 10)

but how can i changed the 4 to a dynamic number found by finding the position of the underscore

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-21 : 21:41:05

[code]
declare @str varchar(10)

select @str = 'yes_no'

select right(@str, len(@str) - charindex('_', @str))
[/code]

nobby
Yak Posting Veteran

58 Posts

Posted - 2007-03-21 : 21:48:26

I have managed to do it this way can you see any problems with this way

SELECT SUBSTRING(MyField, CHARINDEX('_', MyField) + 1, 20) AS Expr1
FROM dbo.MyTable

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-21 : 21:58:00

yes the 20. You are assuming the max number of char after the '_' is 20.


SELECT SUBSTRING(MyField, CHARINDEX('_', MyField) + 1, 20 len(MyField)) AS Expr1
FROM   dbo.MyTable

nobby
Yak Posting Veteran

58 Posts

Posted - 2007-03-21 : 22:49:33

Thanxs khtan

i have made that last change

Thanxs for the help

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-22 : 01:30:39

8000 is fine too.

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-22 : 01:33:18

8000 is fine too if you are using SQL Server 2000

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources