Parsing a URL to find a varying length string

Question

I have a string in multiple URLs starting with two characters followed by between 1-6 numbers e.g. 'SO123456' This string is rarely in the same position within the URL. Following the string is either .html or whitespace.

SELECT SUBSTRING(URL,PATINDEX('%SO[0-9]%',URL),8)

FROM Table

WHERE URL LIKE '%SO[0-9]%'

This code returns 'SO12.htm' if the string is shorter than 8 characters.

Not all of the URLs have this string, and if that's the case then I still need the query to produce 'Null'.

I'm trying to return the exact length of the string. Can anyone help me with a way to solve this please? Can you find the length of a wildcard string to use within the substring so that only the exact string length is returned?

Many Thanks.

Possible duplicate of: http://stackoverflow.com/questions/9772566/using-patindex-to-find-varying-length-patterns-in-t-sql — JimmyB, Jan 12 '16 at 15:56
[This answer](http://stackoverflow.com/a/9778647/1015327) shows how to extract a substring matching a pattern from a string. The same should work for you. Then take the length of that string. — JimmyB, Jan 12 '16 at 17:01
This works for the URLs where my string is the first numeric character. Do you have any idea how to apply this to exactly the string I need? Thanks — Tyler, Jan 13 '16 at 09:59
Of course, you must use *your own* pattern, but apart from that it should work for you. — JimmyB, Jan 13 '16 at 12:25

score 0 · Answer 1 · answered Jan 12 '16 at 15:49

0

I think you should check the length of the column first dude. Here's the code

SELECT CASE LEN(URL) -- Check the length of URL column
WHEN > 8 THEN SUBSTRING(URL,PATINDEX('%SO[0-9]%',URL),8)
ELSE -- do your stuff
END as 'example'
FROM Table

answered Jan 12 '16 at 15:49

Webster

1,113
2
19
39

Every URL is greater than 8 characters, the 8 is the maximum number of characters the string I'm interested can be. – Tyler Jan 12 '16 at 16:02

score 0 · Answer 2 · answered Jan 12 '16 at 16:48

0

Try this: Select case when patindex('%SO[0-9]&',URL) = 0 then null else substring(URL, patindex('%SO[0-9]&',URL),8)

From Table

answered Jan 12 '16 at 16:48

thezapper

486
4
13

JimmyB · Accepted Answer · 2016-01-13T12:21:04.403

Not quite elaborated, but as a hint to get you started:

patindex = PATINDEX('%SO[0-9]%',URL) -> Index of the start of the pattern
charindex = CHARINDEX('.html', URL, patindex ) -> Index of the first '.html' after the start of the pattern.
patternLen = charindex - patindex

So something like the following may work:

SELECT
  CHARINDEX('.html', URL,
    PATINDEX('%SO[0-9]%',URL)
  ) -
  PATINDEX('%SO[0-9]%',URL)
FROM ...

Not all of the URLs have this string, and if that's the case then I still need the query to produce 'Null'.

-> Outer (self) join:

SELECT
  allUrls.URL,
  CHARINDEX('.html', u.URL, PATINDEX('%SO[0-9]%',u.URL) ) - PATINDEX('%SO[0-9]%', u.URL) -- Same as above
FROM MyTable allUrls
LEFT OUTER JOIN MyTable u
  ON allUrls.URL = u.URL
  AND u.URL LIKE '%SO[0-9]%'

Parsing a URL to find a varying length string

3 Answers3