Parse the data and extract a 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 2005 Forums

Transact-SQL (2005)

Parse the data and extract a value.

Author

Topic

tamancha.1
Starting Member

37 Posts

Posted - 2010-09-27 : 13:14:25

other_data
u1=9.618;u2=9186137;u3=Short Term
u1=8.904;u2=9186141;u3=Short Term
u1=424.62;u2=0060743791;u3=Health Access

u1
9.618
8.904
424.62

I need to extract the value of u1 from other_data using SQL? Please help.

I was trying something like this, it is not working:

select substring(other_data,
charindex('=',other_data)+1,charindex(';',other_data)-charindex('=',other_data)-1)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-27 : 13:17:49

SELECT replace(Substring(other_data,1,charindex(';', otherdata)-1),'u1=','')

tamancha.1
Starting Member

37 Posts

Posted - 2010-09-27 : 13:21:23

Msg 536, Level 16, State 5, Line 2
Invalid length parameter passed to the SUBSTRING function.

Please note u3 has a long value in my dataset sometimes, will that create a problem?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-27 : 13:26:37

No, that won't be a problem. The error means that at least one of the rows doesn't have a semicolon in that column. You can overcome the error with:

SELECT replace(Substring(other_data,1,charindex(';', otherdata)-1),'u1=','')
FROM myTable
WHERE other_data LIKE 'u1=%;%'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-28 : 11:27:01

If u1 is not fixed,

select data,substring(data,charindex('=',data)+1,charindex(';',data)-charindex('=',data)-1) as amount from
(
select 'u1=9.618;u2=9186137;u3=Short Term ' as data union all
select 'u2=8.904;u2=9186141;u3=Short Term' union all
select 'u1534=424.62;u2=0060743791;u3=Health Access'
) as t

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources