How to read particular number from string? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

How to read particular number from string?

Author

Topic

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-12-14 : 06:12:09

I have table (log)as below

ID---Qstring----------------------------------------------ProID
1----ID=25375
2----ID=23382
3----lngSessionId=134513044&pid=25375
52---lngSessionId=133466044&pid=27890
67---tduid=dc79b&url=http://www.mydomain.co.uk/proddetails.asp?id=186

How do I read only the number starts with ID and pid from the above string and insert into column 'ProID' of the same row? It is log table tracking all the visiters to the web site. The number next to IDs and pids are productIDs. So if I manage to get product ID to the column ProId then I can hit against other table to find out which product the customer clicked.

I planned to do the above said by trigger. Please help

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-14 : 06:23:10

select substring(col,charindex('ID',col),len(col)) from table

Madhivanan

Failing to plan is Planning to fail

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-12-14 : 06:49:31

Hi

I have tried the below query but not picking up the right result (pasted below)

SELECT SUBSTRING(QueryString, CHARINDEX('ID', QueryString), LEN(999999999)) AS Expr1
FROM Log

Result

ID=23154
id=3499
ID=2763&D
L

id=CLP4iZ
id=4d4624

id=CMi76s
ID=4998
id=3831
id=CMCcjN
id=23306
id=3694

Any other method ??

quote:
Originally posted by madhivanan

select substring(col,charindex('ID',col),len(col)) from table

Madhivanan

Failing to plan is Planning to fail

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-12-14 : 07:11:22

quote:
Originally posted by Vaishu

Hi

I have tried the below query but not picking up the right result (pasted below)

SELECT SUBSTRING(QueryString, CHARINDEX('ID', QueryString), LEN(999999999)) AS Expr1
FROM Log

Result

ID=23154
id=3499
ID=2763&D
L

id=CLP4iZ
id=4d4624

id=CMi76s
ID=4998
id=3831
id=CMCcjN
id=23306
id=3694

IF the above not possible please look at the below example

1 ----txtString=Yuasa&submit&gclid=CPaygrTbp5ACFQ3nlAodTl8GHA
ex: shouldn't pickup anything
2-----ID=22878&Department=
ex: 22878
3-----tuid=68a5&url=http://www.mydomain.co.uk/proddetails.asp?id=186
ex: 186
4-----id=23129
ex: 23129
5-----ID=3905
ex: 3905

If the above not possible can u help me to do the following. Most of the numbers starts like below
ID=3905
ID=390567
ID=23478
ID=22878&Department=

So IS IT POSSIBLE TO GET THE NUMBER after 'ID=' and when strings are like 'ID=22878&Department=' NEED TO GET NUMBER between 'ID=' and '&'
So result will be only number

quote:
Originally posted by madhivanan

select substring(col,charindex('ID',col),len(col)) from table

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-14 : 07:16:33

do you want 23154 instead of ID=23154?

Madhivanan

Failing to plan is Planning to fail

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-12-14 : 07:29:24

Hi
YES

quote:
Originally posted by madhivanan

do you want 23154 instead of ID=23154?

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-14 : 07:33:11

Try this

SELECT SUBSTRING(QueryString, CHARINDEX('ID', QueryString)+3, LEN(999999999)) AS Expr1
FROM Log

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-14 : 08:30:52

continue to http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=94246

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources