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 Problem

Author  Topic 

yellowman
Starting Member

25 Posts

Posted - 2012-11-13 : 11:12:10
I am using a piece of code to parse a number out of a string (and in most situations it works pretty good).
I was under the assumption that each string would only have one number in it. I was wrong and every now
and then I run into a string with multiple numbers in it.

Here is the original code:


SELECT 'CQ_PROD' AS dbname, Left(SUBSTRING(program, PATINDEX('%[0-9.-]%', program), 8000), PATINDEX('%[^0-9.-]%', SUBSTRING(program, PATINDEX('%[0-9.-]%', program), 8000) + 'X')-1) AS Schema_Version
FROM CQ_PROD.DBO.scriptdef
WHERE (program LIKE '%sub JAT_Version%')


These were the kinds of strings that I was parsing:

Jat_Version { # Bunch of text ("Jumpstart Version 5.0"); }
Jat_Version { # Bunch of text ("Jumpstart Version 5.0.1"); }
Jat_Version { # Bunch of text ("Jumpstart Version 4.3"); }

The query fails if the string has something like this:
Jat_Version { # Bunch of text 1,2,3 some more text ("Jumpstart Version 5.0"); }

Is there a quick way to tell my SUBSTRING line to start at the word "Jumpstart" (ignoring case)
instead of the first character in the string. I think this would solve my issues.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-13 : 11:36:03
DECLARE @sql varchar(1000) = 'Jat_Version { # Bunch of text 1,2,3 some more text ("Jumpstart Version 5.0"); }'


SELECT SUBSTRING(@sql,PATINDEX('%Jumpstart Version%',@sql)+18,PATINDEX('%[^0-9.-]%', SUBSTRING(@sql,PATINDEX('%Jumpstart Version%',@sql)+18,8000))-1)


Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

yellowman
Starting Member

25 Posts

Posted - 2012-11-13 : 12:58:38
This was a lot of help and the code works great. Thank you!
Go to Top of Page
   

- Advertisement -