Substring question - 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

General SQL Server Forums

New to SQL Server Programming

Substring question

Author

Topic

Aleph_0
Yak Posting Veteran

79 Posts

Posted - 2011-08-22 : 20:14:57

Hi, I've never had to deal with strings before and I'm having a little trouble. Let's get this part out of the way:


CREATE TABLE #Example (s_index INT, s_query nvarchar(255))
INSERT INTO #Example
SELECT 000001, 'folder=123'
INSERT INTO #Example
SELECT 000002, 'folder=2222'
INSERT INTO #Example
SELECT 000003, 'folder=34343&other_stuff'
INSERT INTO #Example
SELECT 000004, 'folder=202-other_stuff'

I'm only concerned with the s_query field; I need to pull the folder number only and discard anything that follows so that I get:


s_index  folder_id
------------------
000001     123
000002     2222
000003     34343
000004     202

Here's what I did initially:


SELECT
	e.s_query
	, [folder_id_flawed] = Substring(e.s_query
							, Charindex('folder=', e.s_query) + 7				--Start after 'folder='
							,	CASE 
									WHEN Charindex('&', Substring(e.s_query		--When there is a '&' after the folder number
										, Charindex('folder=', e.s_query) + 8
										, LEN(e.s_query))
										) > 0
									THEN Charindex('&', Substring(e.s_query		--Then stop before the '&'
										, Charindex('folder=', e.s_query) + 8
										, LEN(e.s_query))
										)
									ELSE LEN(e.s_query) - 7						--Otherwise go until the end of the string
								END
							)
FROM #Example e
WHERE 1=1
	AND ( (e.s_query LIKE 'folder=%') OR (e.s_query LIKE '%&folder=%') )

And the result of that was:


s_index  folder_id_flawed
------------------
000001     123
000002     2222
000003     34343
000004     202-other_stuff

Now, since my sample data set was so small, I thought the only character that could follow the folder number was '&'. Now I'm working with a larger (but still sample) set and noticed that '-' could follow it as well. Is there a way to check for any non-number character? I'd hate to write several nearly-identical WHEN statements.

Thanks!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-22 : 20:39:13

You can use PATINDEX, which allows character classes. Also, using a combination of STUFF and LEFT/RIGHT as appropriate may make it shorter. For your example:


SELECT LEFT
       (
           STUFF(s_query, 1, CHARINDEX(s_query, 'folder=') + 7, ''),
           PATINDEX(
               '%[^0-9]%',
               STUFF(s_query, 1, CHARINDEX(s_query, 'folder=') + 7, '') + 'X'
           ) -1
       )
FROM   #Example

The line in red is looking for any character that is not between 0 and 9.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-23 : 00:09:26

for removing the first part you can just use

REPLACE(s_query,'folder=','')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Aleph_0
Yak Posting Veteran

79 Posts

Posted - 2011-08-23 : 11:55:34

Genius! Thanks, fellas. The book I have (Beginning T-SQL) mentioned PATINDEX but apparently didn't describe everything it can do. Glad I can ask experts here!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources