Posted by Erland Sommarskog on 03/21/06 00:49

(chudson007@hotmail.com) writes:
> I've just encountered a little problam.
> Contrary to the criteria I provided earlier there are cells which end
> with the TranID, like for example
>
> OtherID=1638256785230&TranID=12345
>
> How do I edit the script to capture these records?

Here is a query, a little different from figitals:

SELECT convert(int, str2)
FROM (SELECT str2 =
CASE WHEN str1 LIKE '%[^0-9]%'
THEN substring(str1, 1, patindex('%[^0-9]%', str1) - 1)
ELSE str1
END
FROM (SELECT str1 = substring(str,
charindex('TranID=', str) + len('TranId='),
len(str))
FROM QueryStringTest) AS a) AS b

By using nested derived tables, it is possibly easier to see the
solution step for step. Or it's more confusing. :-)


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: Check constraint does not work (compare with null)
• Prev in forum: Re: waittype=WRITELOG
• Thread view: Re: Help with a substring query

[Reply to this message]