selecting a substring value - 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)

selecting a substring value

Author

Topic

SQL_muppet
Starting Member

1 Post

Posted - 2009-05-28 : 07:08:18

Hi I'm pretty new to TSQL. I use a few other languages but am having a hard time making the jump from Python, VB programming to SQL.

I have a data base table with several fields, one of which contains a delimited string. I'm trying to select records based on the 2nd value in the string. The delimited string doesn't have a consistent number of tokens
e.g
FName, LName, 21:4:blue
FName1, LName1, 23:4:green:empty
etc

I'd like to select all records where the the second token is 4
I can use substring to get the 4 but can't work out how to turn the value into a variable or combine with a where clause. This is something that I can't imagine is that difficult but I can't seem to find where to start in TSQL.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-28 : 07:37:01

[code]DECLARE @Sample TABLE
(
Data VARCHAR(MAX)
)

INSERT @Sample
SELECT 'FName, LName, 21:0:4:blue' UNION ALL
SELECT 'FName, LName, 21:0:45:blue' UNION ALL
SELECT 'FName, LName, 21:0:45' UNION ALL
SELECT 'FName, LName, 21:0:4' UNION ALL
SELECT 'FName1, LName1, 23:0:4:green:empty' UNION ALL
SELECT 'FName, LName, 21:4:blue' UNION ALL
SELECT 'FName, LName, 21:45:blue' UNION ALL
SELECT 'FName, LName, 21:45' UNION ALL
SELECT 'FName, LName, 21:4' UNION ALL
SELECT 'FName1, LName1, 23:4:green:empty' UNION ALL
SELECT 'FName, LName, 4:blue' UNION ALL
SELECT 'FName, LName, 45:blue' UNION ALL
SELECT 'FName, LName, 45' UNION ALL
SELECT 'FName, LName, 4' UNION ALL
SELECT 'FName1, LName1, 4:green:empty'

DECLARE @Token INT
SET @Token = 2

SELECT *
FROM @Sample
WHERE dbo.fnParseString(-2, ':', Data) = '4'[/code]

Function fnParseString is found here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources