Extracting a number from a string value.

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 2000 Forums

SQL Server Development (2000)

Extracting a number from a string value.

Author

Topic

m.ibbrahim
Starting Member

16 Posts

Posted - 2006-05-08 : 02:53:20

Hi,
I am having a field Information(varchar) that has data such as:

"CG:CBRC9SY88 DG: 9.6 KBPS CONT: M".

Now, I have to create a new column Speed, that would contain the Speed value from the Information i.e 9.6 KBPS in above example.
The speed value can be either in terms of Kbps/Mbps and it may occur at any place in the Information.So, I need to search for the terms Kbps/ Mbps and extract the value in front of it..

Regards.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-08 : 03:16:40

You can create a UDF to perform the extraction

declare	@data	varchar(100)

select	@data	= 'CG:CBRC9SY88 DG: 9.6 KBPS CONT: M'

select	right(data1, b) + 'KBPS'
from
(
	select	data1, patindex('%[A-Z:]%', reverse(data1)) - 1 as b
	from
	(
		select	left(@data, len(@data) - a) as data1
		from
		(
			select	charindex('SPBK', reverse(@data)) + 3 as a
		) a1
	) b1
) c1

m.ibbrahim
Starting Member

16 Posts

Posted - 2006-05-08 : 03:45:40

Hi,
it's giving :
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-08 : 03:54:39

quote:
Originally posted by m.ibbrahim

Hi,
it's giving :
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation

can you post the code you used ?

m.ibbrahim
Starting Member

16 Posts

Posted - 2006-05-08 : 04:15:38

It's the same one..

declare @data varchar(1000)
select @data='MALTON 9.6 KBPS M'
select right(data1,b)+'KBPS'
from
{ select data1,patindex('%[A-Z:]%',reverse(data1))-1 as b
from
{ select left(@data,len(@data)-a) as data1
from
{ select charindex('SPBK',REVERSE(@data))+3 as a
}a1

}b1
}c1

Also, the speed value can occur anywhere in the string..and the only way we can recognise a number in that string as speed is that :
A speed value is followed by 'kbps'/'mbps'.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-08 : 04:18:32

One main different. use ( and ) instead of { and }

m.ibbrahim
Starting Member

16 Posts

Posted - 2006-05-08 : 04:39:48

ya, it's working..but it need's tuning..

I was testing the script for various strings.

For:
1. 'TO 514-593 1.344 1.54 MBPS (DS-1) '
the output is:
514-593 1.344 1.54 KBPS
where as I need 1.54 KBPS
2. 'LSO:604-270 SERV: M 9.6 KBPS CG: VANCALG#7'
the output is:
9.6 KBPS
this one os alright.
3. 'UNDER BTN Q-LSO: - TAR: ASSOC WITH 01FWCE840801 CHAN GRP MONHAM1 - 64 KBPS'
the output isL
1 - 64 KBPS
the required ouput is 64 kbps.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-08 : 07:12:02

Here's another way...

You can wrap this in a function like khtan has shown you.

--data
declare @t table (v varchar(100))
insert @t
          select 'CG:CBRC9SY88 DG: 9.6 KBPS CONT: M'
union all select 'TO 514-593 1.344 1.54 MBPS (DS-1) '
union all select 'LSO:604-270 SERV: M 9.6 KBPS CG: VANCALG#7'
union all select 'UNDER BTN Q-LSO: - TAR: ASSOC WITH 01FWCE840801 CHAN GRP MONHAM1 - 64 KBPS'
union all select '1 KBPS'
union all select 'abc 2 KBPS'
union all select '3 KBPS abc'

--calculation
select ltrim(reverse(left(v2, charindex(' ', v2, 6)))) as Speed, * from (
    select *, reverse(' ' + left(v, patindex('%[MK]BPS%', v)+3)) as v2 from @t) a

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

m.ibbrahim
Starting Member

16 Posts

Posted - 2006-05-08 : 08:57:38

quote:

Originally posted by RyanRandall

Here's another way...

You can wrap this in a function like khtan has shown you.

--data
declare @t table (v varchar(100))
insert @t
          select 'CG:CBRC9SY88 DG: 9.6 KBPS CONT: M'
union all select 'TO 514-593 1.344 1.54 MBPS (DS-1) '
union all select 'LSO:604-270 SERV: M 9.6 KBPS CG: VANCALG#7'
union all select 'UNDER BTN Q-LSO: - TAR: ASSOC WITH 01FWCE840801 CHAN GRP MONHAM1 - 64 KBPS'
union all select '1 KBPS'
union all select 'abc 2 KBPS'
union all select '3 KBPS abc'

--calculation
select ltrim(reverse(left(v2, charindex(' ', v2, 6)))) as Speed, * from (
    select *, reverse(' ' + left(v, patindex('%[MK]BPS%', v)+3)) as v2 from @t) a

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Thankx RyanRandall..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-09 : 01:39:48

Also refer
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56713

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources