Substring length unknown

[amylashley]

Is there a way using SQL Queries to extract a substring when the length of it is not known? It seems like such a simple thing but all I see is a SUBSTRING function that requires knowledge of the length of the substring in advance.

Thanks

 

[JohnDTampaBay]

The LEN() function will return the length of a string. CHARINDEX and PATINDEX will return the position of a string within another string. Can you combine these functions to produce the results you're looking for? Good luck!

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[amylashley]

Ok thanks. I didn't need the LEN function. But the PatINDEX helped:

KeywordBuys.URL,SUBSTRING(KeywordBuys.URL,charIndex('landerName',KeywordBuys.URL)+11,PATINDEX ( '%&misc%' , KeywordBuys.URL )-(charIndex('lander',URL )+11))