Extracting Text - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Extracting Text

Author

Topic

kswenso
Starting Member

5 Posts

Posted - 2009-07-20 : 10:23:33

I have a URL field in a table, here are 2 examples.

/selfservice/common/viewdocument_forFrameset_Metadata.jsp?externalId=3242416&sliceId=1&dialogID=69798163&docType=kc&cmd=displayKC&highlight=on&stateId=1+0+69790583

/selfservice/microsites/search.do?cmd=displayKC&docType=kc&externalId=6882051&sliceId=1&docTypeID=DT_PRODUCTSUPPORT_1_1&dialogID=280854110&stateId=1%200%20280856000&highlight=on

I need to extract the externalid= and grab all the numbers up to the & and place it in its own separate field. In the first URL I would need 3242416 and the second one 6882051. Can anyone help?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-20 : 10:35:08

If the length of number is always 7

select substring(data ,CHARINDEX('externalId',data)+11,7)
from
(
select '/selfservice/common/viewdocument_forFrameset_Metadata.jsp?externalId=3242416&sliceId=1&dialogID=69798163&docType=kc&cmd=displayKC&highlight=on&stateId=1+0+69790583' as data
union all
select '/selfservice/microsites/search.do?cmd=displayKC&docType=kc&externalId=6882051&sliceId=1&docTypeID=DT_PRODUCTSUPPORT_1_1&dialogID=280854110&stateId=1%200%20280856000&highlight=on'
) as t

Madhivanan

Failing to plan is Planning to fail

kswenso
Starting Member

5 Posts

Posted - 2009-07-20 : 10:50:55

ExternalID does not always have a length of 7. Here is a different URL i have. Is there anyway to make it find externalid and grab everything up to & character?

/selfservice/common/viewdocument_forFrameset_Metadata.jsp?externalId=GA698&sliceId=1&dialogID=280854315&docType=kc&cmd=displayKC&highlight=on&docTypeID=DT_PRODUCTSUPPORT_1_1&stateId=1+0+280856016

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-20 : 10:57:14

select substring(data ,1,CHARINDEX('&',data)-1) as data from
(
select substring(data ,CHARINDEX('externalId',data)+11,LEN(data)) as data
from
(
select '/selfservice/common/viewdocument_forFrameset_Metadata.jsp?externalId=3242416&sliceId=1&dialogID=69798163&docType=kc&cmd=displayKC&highlight=on&stateId=1+0+69790583' as data
union all
select '/selfservice/microsites/search.do?cmd=displayKC&docType=kc&externalId=6882051&sliceId=1&docTypeID=DT_PRODUCTSUPPORT_1_1&dialogID=280854110&stateId=1%200%20280856000&highlight=on'
union all
select '/selfservice/common/viewdocument_forFrameset_Metadata.jsp?externalId=GA698&sliceId=1&dialogID=280854315&docType=kc&cmd=displayKC&highlight=on&docTypeID=DT_PRODUCTSUPPORT_1_1&stateId=1+0+280856016'
) as t
) as t

Madhivanan

Failing to plan is Planning to fail

kswenso
Starting Member

5 Posts

Posted - 2009-07-20 : 15:13:00

Madhivanan -

You have been a ton of help so far, one last question. I have the following query now but I receive an error on the first line where I have -1. If i change it to +1 the query runs but of course give the incorrect results. The error is "invalid length parameter passed to substring function."

SELECT SUBSTRING(data, 1, CHARINDEX('&', data) - 1) AS data
FROM (SELECT SUBSTRING(Data, CHARINDEX('externalId', Data) + 11, LEN(data)) AS data
FROM (SELECT URL AS Data
FROM arch_einstein_localhost_access_log_1) t) t

If you have any ideas please let me know...Thanks

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-07-20 : 16:10:11

It appears you do not always have "externalId" in ur Data field. In that case the CHARINDEX would return 0, giving an invalid value of -1 in the substring statement.

What do you want to do if there is no "externalId" in the Data field?

kswenso
Starting Member

5 Posts

Posted - 2009-07-21 : 09:08:48

Quickly scanning through the field it looks like I have an externalid in each field, but if there isn't how to do I set it to 0000000?

Thanks a lot for the help....

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-21 : 09:27:25

Try this

select substring(data ,1,case when CHARINDEX(')',data)>0 then CHARINDEX('&',data)-1 else 0 end) as data from
FROM (SELECT SUBSTRING(Data, CHARINDEX('externalId', Data) + 11, LEN(data)) AS data
FROM (SELECT URL AS Data
FROM arch_einstein_localhost_access_log_1) t) t

Madhivanan

Failing to plan is Planning to fail

kswenso
Starting Member

5 Posts

Posted - 2009-07-21 : 09:38:36

This seem to work, thanks for all the help!

SELECT SUBSTRING(data, 1, CHARINDEX('&', data) - 1) AS data
FROM (SELECT SUBSTRING(Data, CHARINDEX('externalId', Data) + 11, LEN(data)) AS data
FROM (SELECT URL AS Data
FROM arch_einstein_localhost_access_log_1) t
WHERE CHARINDEX('&', data) > 0) t

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources