Get Number from string ? - 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

General SQL Server Forums

New to SQL Server Programming

Get Number from string ?

Author

Topic

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-12-14 : 07:51:25

Hi

ID=2763&Department=SLA

How do I get number between 'ID=' and '&' from the above string. The digits vary from 2 to 6 digits

Advance Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-14 : 07:59:00

use charindex to find the position after = and & and use substring to extract

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

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-12-14 : 08:06:41

Hi

I am using below to read after ID but how do I alter the below query to detect '&'.

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

quote:
Originally posted by khtan

use charindex to find the position after = and & and use substring to extract

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-14 : 08:11:10

SELECT SUBSTRING(QueryString, CHARINDEX('ID', QueryString)+3,CHARINDEX('&', QueryString)-4) AS Expr1
FROM Log

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-14 : 08:11:36

[code]declare @str varchar(40)

select @str = 'ID=2763&Department=SLA'

select substring(@str, charindex('ID=', @str) + 3,
charindex('&', @str) - charindex('ID=', @str) - 3)[/code]

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-14 : 08:12:55

or if the string always begin with 'ID='


select	left(replace(@str,'ID=', ''), charindex('&', replace(@str,'ID=', '')) - 1)

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-14 : 08:26:42

and regardless of any position


declare @v varchar(100)
set @v='lkASDKLJKLJDDEFF09127878734SKLDJF'

select left(v,patindex('%[^0-9]%',v)-1) from
(
	select substring(@v,patindex('%[0-9]%',@v),len(@v)) as v
) as t

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

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

oh . . it is continue from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=94242

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-14 : 08:34:39

quote:
Originally posted by khtan

oh . . it is continue from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=94242

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

Yes it is.
I thought I was answering to the same thread

Madhivanan

Failing to plan is Planning to fail

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-12-14 : 09:35:43

Hi

Silly question !!!. Do I have to use Sql procedure to use your code.
What is 'v' and t stands for. Sorry for hassle, I am desperate to sort this out.

Thank you

quote:
Originally posted by madhivanan

and regardless of any position

declare @v varchar(100)
set @v='lkASDKLJKLJDDEFF09127878734SKLDJF'

select left(v,patindex('%[^0-9]%',v)-1) from
(
select substring(@v,patindex('%[0-9]%',@v),len(@v)) as v
) as t

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-14 : 09:37:51

quote:


declare @v varchar(100)
set @v='lkASDKLJKLJDDEFF09127878734SKLDJF'

select left(v,patindex('%[^0-9]%',v)-1) from
(
	select substring(@v,patindex('%[0-9]%',@v),len(@v)) as v
) as t

v is the column alias

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

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-12-14 : 09:45:32

I used the code as below but how do I asign column name to V
QueryString is the columname

CREATE PROCEDURE sp_Pageviews

As

declare @v varchar(100)
set @v=

select left(QueryString,patindex('%[^0-9]%',QueryString)-1) from
(
select substring(@v,patindex('%[0-9]%',@v),len(@v)) as QueryString
) as t
GO

quote:
Originally posted by khtan

quote:

declare @v varchar(100)
set @v='lkASDKLJKLJDDEFF09127878734SKLDJF'

select left(v,patindex('%[^0-9]%',v)-1) from
(
select substring(@v,patindex('%[0-9]%',@v),len(@v)) as v
) as t

v is the column alias

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-14 : 09:48:59

replace v with your actual column name


select left(QueryString, patindex('%[^0-9]%', QueryString)-1) 
from   Log

Ignore this. C&P error

Think i better stop posting for the day.

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-14 : 09:53:23

quote:
Originally posted by Vaishu

I used the code as below but how do I asign column name to V
QueryString is the columname

CREATE PROCEDURE sp_Pageviews

As

declare @v varchar(100)
set @v=

select left(QueryString,patindex('%[^0-9]%',QueryString)-1) from
(
select substring(@v,patindex('%[0-9]%',@v),len(@v)) as QueryString
) as t
GO

quote:
Originally posted by khtan

quote:

declare @v varchar(100)
set @v='lkASDKLJKLJDDEFF09127878734SKLDJF'

select left(v,patindex('%[^0-9]%',v)-1) from
(
select substring(@v,patindex('%[0-9]%',@v),len(@v)) as v
) as t

v is the column alias

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

CREATE PROCEDURE sp_Pageviews

As

select QueryString,left(v,patindex('%[^0-9]%',v)-1) as number_only from
(
select QueryString,substring(QueryString,patindex('%[0-9]%',QueryString),len(QueryString)) as v from [log]
) as t
GO

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-14 : 10:02:09

quote:
Originally posted by khtan

replace v with your actual column name

select left(QueryString, patindex('%[^0-9]%', QueryString)-1)
from Log

Ignore this. C&P error Think i better stop posting for the day.

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

I think you need a break today

Madhivanan

Failing to plan is Planning to fail

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-12-14 : 10:30:13

CREATE PROCEDURE sp_logAs

select substring(QueryString, charindex('ID=', QueryString) + 3,
charindex('&', QueryString) - charindex('ID=', QueryString) - 3) from log
GO

I am getting error message for the above query

'Invalid length parameter passed to the substring function.'

Column querystring is nvarchar(255) in my table but it WORKS OK IF I DECLARE A STRING

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-15 : 01:17:49

Did you run the method posted by me?

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-15 : 01:52:34

quote:
Originally posted by madhivanan

quote:
Originally posted by khtan

replace v with your actual column name

select left(QueryString, patindex('%[^0-9]%', QueryString)-1)
from Log

Ignore this. C&P error Think i better stop posting for the day.

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

I think you need a break today

Madhivanan

Failing to plan is Planning to fail

Yeah, it was a long day and late at night (or rather early in the morning)

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

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-12-18 : 04:45:43

Hi MADHI

Yes I did but it takes the first number from the string.

ex: tduid=14edd0f11ea319bbf0&url=http://www.somedomain.co.uk/proddetails.asp?id=332

For the above example I need number(332) after the '?id=' but the code picking up 14 which is after the 'tduid='

I NEED AS BELOW:

I NEED to take the numbers from the string followed by '?id=' and some string starts with ID=23456, some strings are ID=3646&Department= . SO IF I AM ABLE TO MEET THIS 3 CRITEREA to get only the numbers thats enough!!

quote:
Originally posted by madhivanan

Did you run the method posted by me?

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-18 : 07:32:35

You need to apply queries like

SELECT SUBSTRING(QueryString, CHARINDEX('?ID', QueryString)+4,len(Querystring)) AS Querystring
FROM (select 'tduid=14edd0f11ea319bbf0&url=http://www.somedomain.co.uk/proddetails.asp?id=332' as Querystring) as t

SELECT SUBSTRING(QueryString, CHARINDEX('ID', QueryString)+3,len(Querystring)) AS Querystring
FROM (select 'tduiasdd=14edd0f11ea319bbf0&url=http://www.somedomain.co.uk/proddetails.aspid=332' as Querystring) as t

Madhivanan

Failing to plan is Planning to fail

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-12-18 : 07:52:00

Hi Madhi I will try your last suggestion. In the mean time Could you please have a look at this as well?

How do get only the required part of the string as I explaind below. (Needed strings are followed by i.e as below). I am about to post this as a new thread

SO IF ANYONE LET ME KNOW HOW TO USE IF or CASE with 'charindex' or any other method to get the above said result would be greatfull.

txtString=duracell+alkaline+battery+&submit1=Search&OVR31&OVKWID=24137211531
i.e : duracell+alkaline+battery+

txtString=sealed+lead+acid+batteries+&submit&gclid=CMekn-fesZAodpzIcHA
i.e : sealed+lead+acid+batteries+

ID=3&Department=
i.e : 3

ID=22893
i.e : 22893

lngSessionId=837541253&pid=22714
i.e : 22714

CCcode=502&OrderId=INT10350&puramt=1773
i.e : INT10350

tduid=5c14526847651e9054552acc134e9a84&url=http://www.somedomain.co.uk/proddetails.asp?id=4204
i.e : 4204

Code I am using (Got this one from my other posting)
declare @QueryString nvarchar(255)

select @QueryString = 'ID=23456&Depatment='

select substring(@QueryString, charindex('ID=', @QueryString) + 3,
charindex('&', @QueryString) - charindex('ID=', @QueryString) - 3) from Pageviews

1. But the above code only works if the string starts with 'ID='.
2. Because the col1 has strings starts with different letter this code fail to excute and gives error
3. Pathindex with regular express '(patindex('%[^0-9]%',v)-1)' cannot be used because it gets anynumber
in the string - string has mixer of numbers and letters

quote:
Originally posted by madhivanan

You need to apply queries like

SELECT SUBSTRING(QueryString, CHARINDEX('?ID', QueryString)+4,len(Querystring)) AS Querystring
FROM (select 'tduid=14edd0f11ea319bbf0&url=http://www.somedomain.co.uk/proddetails.asp?id=332' as Querystring) as t

SELECT SUBSTRING(QueryString, CHARINDEX('ID', QueryString)+3,len(Querystring)) AS Querystring
FROM (select 'tduiasdd=14edd0f11ea319bbf0&url=http://www.somedomain.co.uk/proddetails.aspid=332' as Querystring) as t

Madhivanan

Failing to plan is Planning to fail

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources