Parse Text in a URL

I need to extract a product #, which is in the URL. I figured most of it out but can't get one thing to work. The URLs are the format as below.

http://www.MyWebsite.com/p/Some-Text/-/A-31342578#Somevalue=Big_1_10

I need to extract the 31342578, which appears after the "A-" and ends at the "#". Below is the SQL that I have come up with this far.

Select 	 URL					
  SUBSTRING(  ltrim(rtrim(
	substring(
		replace(cast(WL.URL as nvarchar(max)),
		'/',
		replicate(cast(' ' as nvarchar(max)),10000)),
					60001, 10000))),0,CHARINDEX('#',ltrim(rtrim(
	substring(
		replace(cast(WL.URL as nvarchar(max)),
		'/',
		replicate(cast(' ' as nvarchar(max)),10000)),
					60001, 10000)))))
From tblWebLinks WL
With the above URL, this would return A-31342578. It's close but I need to remove the "A-". If anyone can offer me some assistance, I'd appreciate it. Thanks.

August 28th, 2015 8:41am

Hi,

maybe you can use something like this.

DECLARE @url VARCHAR(100) = 'http://www.MyWebsite.com/p/Some-Text/-/A-31342578#Somevalue=Big_1_10'

SELECT LEFT(RIGHT(@url,27),8)

Free Windows Admin Tool Kit Click here and download it now
August 28th, 2015 8:47am

It's not a static URL. These are URLs that are in a table. That was just an example that I posted. They're all different but will be in the same exact format as the example where it appears after the 6th "/" with the "A-" appearing right before and the "#" appearing at the end.
August 28th, 2015 9:04am

Try this out ..assuming the format of the url is always same..

set @url = 'http://www.MyWebsite.com/p/Some-Text/-/A-31342578#Somevalue=Big_1_10'

set @url = reverse(left(reverse(@url), charindex('/', reverse(@url)) -1))

SELECT SUBSTRING(
    @url, 
    CHARINDEX('-', @url) + 1, 
    LEN(@url) - CHARINDEX('-', @url) - CHARINDEX('#', REVERSE(@url))
	)

Free Windows Admin Tool Kit Click here and download it now
August 28th, 2015 9:12am

DECLARE @URL VARCHAR(1000)
SET @URL = 'http://www.MyWebsite.com/p/Some-Text/-/A-31342578#Somevalue=Big_1_10'

SELECT SUBSTRING(@URL, CHARINDEX('A-',@URL) + 2, CHARINDEX('#',@URL) - CHARINDEX('A-',@URL) - 2)

August 28th, 2015 9:15am

Try this

CREATE FUNCTION [dbo].[Split_fn](
          @delimited NVARCHAR(MAX),
          @delimiter NVARCHAR(100)
        ) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
        AS
        BEGIN
          DECLARE @xml XML
          SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'

          INSERT INTO @t(val)
          SELECT  r.value('.','varchar(MAX)') as item
          FROM  @xml.nodes('/t') as records(r)
          RETURN
        END


GO

DECLARE @url varchar(max);
SET @url = 'http://www.MyWebsite.com/p/Some-Text/-/A-31342578#Somevalue=Big_1_10'



SELECT 
	Product = SUBSTRING(s.val,3,CHARINDEX('#',s.val)-3)
FROM dbo.Split_fn(@url,'/') s
WHERE s.val LIKE 'A-%'

Free Windows Admin Tool Kit Click here and download it now
August 28th, 2015 9:16am

select Substring(WL.url, charINDEX('A-',WL.url)+2,  charINDEX('#',WL.url)-charINDEX('A-',WL.url)-2 ) asNumber_From_URL
From tblWebLinks WL

August 28th, 2015 9:18am

Thanks for the responses. I'm sorry but I'm not understanding how that would work. I see a static URL being set. Is there a way to just modify what I already have to remove the "A-"?

Free Windows Admin Tool Kit Click here and download it now
August 28th, 2015 9:22am

SELECT SUBSTRING(WL.URL, CHARINDEX('A-', WL.URL) + 2, CHARINDEX('#', WL.URL) - CHARINDEX('A-', WL.URL) - 2)
FROM tblWebLinks WL
August 28th, 2015 9:27am

Ok, The responses from Jingyang and Guoxiong, which look to be the same, I think are exactly what I need. The results will flash up for a second and then the below message appears.

Msg 537, Level 16, State 3, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.

Free Windows Admin Tool Kit Click here and download it now
August 28th, 2015 9:33am

Are you sure the number must be between "A-" and "#"? I'm wondering that some URLs may not follow this format.
August 28th, 2015 10:07am

You're right. That's exactly what it is. Just like a handful of records causing it. Any suggestions?
Free Windows Admin Tool Kit Click here and download it now
August 28th, 2015 11:06am

select Substring(WL.url, charINDEX('A-',WL.url)+Case when charINDEX('A-',WL.url)>0 then 2 else 1 End  ,  Case when charINDEX('#',WL.url)-charINDEX('A-',WL.url)-2>0 then charINDEX('#',WL.url)-charINDEX('A-',WL.url)-2 Else len(URL) ENd ) asNumber_From_URL
From tblWebLinks WL

August 28th, 2015 11:16am

You're right. That's exactly what it is. Just like a handful of records causing it. Any suggestions?

You mean that the number in the URL does not follow that rule? If yes, can you list all of the situations?
Free Windows Admin Tool Kit Click here and download it now
August 28th, 2015 1:44pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics