SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Substring Problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

yellowman
Starting Member

USA
25 Posts

Posted - 11/13/2012 :  11:12:10  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/13/2012 :  11:36:03  Show Profile  Reply with Quote
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

USA
25 Posts

Posted - 11/13/2012 :  12:58:38  Show Profile  Reply with Quote
This was a lot of help and the code works great. Thank you!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000