getting substring of column data

Question

getting substring of column data

tracmonali

Mr or Mrs. 500

Points: 551
More actions
February 4, 2014 at 9:05 pm

#396223

I wish to get a substring of data from a column which has URL.
example: http://www.xyz.com/region/department.asp?FID=8907 is the data in column "logpage"
I wish to retrieve only department.asp and trim anything that is before and after it. so ?FID=8907 and http://www.xyz.com/region/ shouldn't be in the derieved column. Later I will be joining this column "logpage" to another column. how can I achieve this?
The column logpage has various different urls. So how a substring function can be used without knowing start , length.
Any pointers appreciated.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Do all of these URLs have the ? following the asp? Or do you have different page extensions too such as htm html asp etc?

That information will play into the answer.

Maybe a good thing to do here would be to provide a sample of 10 different urls that need to be substringed.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

tracmonali Mr or Mrs. 500 Points: 551 More actions · Answer 2

no all of them will have .asp as extension but the part before and after it would change. I do understand that .asp will be my driver. But the length and start information cannot be determined.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Well this should work for you

DECLARE @blah TABLE (someblah VARCHAR(256))

INSERT INTO @blah

VALUES ('http://www.xyz.com/region/department.asp?FID=8907')

SELECT REVERSE(SUBSTRING(REVERSE(someblah),CHARINDEX('?',REVERSE(someblah),1)+1

,CHARINDEX('/',REVERSE(someblah))-CHARINDEX('?',REVERSE(someblah),1)-1))

FROM @blah

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

tracmonali Mr or Mrs. 500 Points: 551 More actions · Answer 4

Thanks. This works fine with 1 input. However, when reading all rows from column name, I get this error: "Invalid length parameter passed to the SUBSTRING function"

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

You probably have some null values in the table. Or you have some urls that do not fit that pattern.

Having a sample set of data would have helped to resolve those things and create a better solution.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

tracmonali Mr or Mrs. 500 Points: 551 More actions · Answer 6

Yes, some Urls have different patterns. It is not consistent except that there is .asp in all the rows. Here is some example:

http://www.xyz.com/tyeocpoute/mailserverices.asp

http://www.pqr.com/typecm/allofthem.asp?Info=4Fhs+fyOPWQRTcxcT

/errorpages.asp

/trwmjh.asp?Info=m/Txnxt1AKNvdWkS

http://www.xyz.com/hhhnnnnn.asp?Info=sHIHAfMVQY++zIeC

Notice only the pages with .asp extension is intended to be retrieved. no other information is consistent.

Thanks

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

Of that small sample set, the following is throwing a curveball

/trwmjh.asp?Info=m/Txnxt1AKNvdWkS

The issue is the / that comes after the ?. Is this a valid url?

In any case, this should get you closer

DECLARE @blah TABLE (someblah VARCHAR(256))

INSERT INTO @blah

VALUES ('http://www.xyz.com/region/department.asp?FID=8907')

,('www.xyz.com/tyeocpoute/mailserverices.asp')

,('/trwmjh.asp?Info=m/Txnxt1AKNvdWkS')

SELECT REVERSE(SUBSTRING(REVERSE(someblah),CHARINDEX('?',REVERSE(someblah),1)+1

,CHARINDEX('/',REVERSE(someblah),ISNULL(CHARINDEX('=',REVERSE(someblah),1)+1,0))

-CHARINDEX('?',REVERSE(someblah),1)-1)

)

FROM @blah

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

tracmonali Mr or Mrs. 500 Points: 551 More actions · Answer 8

Thanks a lot. Can we have say a replace with '' for things that follow with ?. And then use your logic?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

Not sure I fully understand your question.

But if you mean replace everything after the ? with an empty string, then it wouldn't be very useful or efficient. You typically need to know what you are replacing and not just every sequence regardless. You could do a type of regex replace for every character but that would be highly inefficient.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

tracmonali Mr or Mrs. 500 Points: 551 More actions · Answer 10

Thanks. There are about 150,000 rows with same type of urls that I gave you. The pattern is similar. I worked your new code. It gives me correct ans when I put in URL, but if I replace it with the column name and use

select (your code( column name)) from table it gives me "Invalid length parameter passed to the SUBSTRING function". Any advise?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

That would mean that you have other urls that do not fit the patterns you have provided. I couldn't tell you what is different not having much of a sample set, or table definition to go on.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events