Parse 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

SQL Server 2000 Forums

Transact-SQL (2000)

Parse string

Author

Topic

Session101
Starting Member

15 Posts

Posted - 2004-10-07 : 13:14:45

I am trying to parse a string that is found in a column. An example of an entry is: seasoned ;servicer_name_1=WELL

I would like to capture only seasoned ;
This is my current SQL statement:
SELECT SUBSTRING(addl_info,
CHARINDEX ('name_comment=',addl_info)+13,
CHARINDEX(';',addl_info,CHARINDEX('name_comment=',addl_info))-CHARINDEX('name_comment=',addl_info)+13)
FROM description

Please let me know what I am doing wrong when computing the end of the string. Thanks,

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-07 : 13:32:45

You gave us a sample that has nothing to do with your the SELECT you are using (where's "name_comment" in your exampple), and the SELECT you are using that you gave us doesn't contain a valid expression.

please give us a few rows of sample data and the actual SELECT you have tried. Please allow for your sample data to cover all possibilities you need to handle.

- Jeff

Session101
Starting Member

15 Posts

Posted - 2004-10-07 : 13:36:52

My apologies, I didn't copy the entire column. Here is a sample entry:
addl_info
-------------
name_comment=seasoned ;servicer_name_1=WELL

I am trying to only extract what name_comment equals. Thus, for this example, I would like to extract seasoned ;

Thanks.

X002548
Not Just a Number

15586 Posts

Posted - 2004-10-07 : 13:42:33

[code]

DECLARE @target varchar(50), @string varchar(255)
SELECT @target = 'seasoned ;', @string = 'seasoned ;servicer_name_1=WELL'
SELECT SUBSTRING(@string,CHARINDEX(@target,@string), LEN(@target))
SELECT @target = 'servicer_name'
SELECT SUBSTRING(@string,CHARINDEX(@target,@string), LEN(@target))

[/code]

Brett

8-)

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-10-07 : 13:45:02

[code]declare @val varchar(100); set @val = 'name_comment=seasoned ;servicer_name_1=WELL'
select substring(@val,14,charindex(';',@val,1)-14)
[/code]

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-07 : 13:48:26

This too!!


declare @val varchar(100); 
set @val = 'name_comment=seasoned ;servicer_name_1=WELL'

Select parsename(replace(parsename(replace(@val,';','.'),2),'=','.'),1)

Corey

Session101
Starting Member

15 Posts

Posted - 2004-10-07 : 15:02:37

Thank you all for your quick replies. I just have one more inquiry. I was using this solution that was provided above:
declare @val varchar(100); set @val = 'name_comment=seasoned ;servicer_name_1=WELL'
select substring(@val,14,charindex(';',@val,1)-14)

My problem is I do not know the length of the string for the substring parameter. For example,

addl_info
------------
name_comment=seasoned ;servicer_name_1=WELL
name_comment=z;servicer_name_1
name_comment=yes

Thanks,

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-10-07 : 15:07:55

If you append the value with a ';' before you perform the parse it will work.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-07 : 16:16:24

do you see why I asked you for several sample rows, covering all possibilities? Are there other variations we need to allow?

for example, some other "name=value" pair before the name_comment? Values with no name_comment at all?

- Jeff

Session101
Starting Member

15 Posts

Posted - 2004-10-07 : 16:34:28

Yes, name_comment could be blank. I take care of that in a case statement.

Also, name_comment could be the first entry, 2nd entry or 3rd entry in the outputted field.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-07 : 16:45:41

since I suspect you may need to do this in multiple places, a UDF might be the best way to go.

Something like:


CREATE FUNCTION GetValue(@Name varchar(100), @Expression varchar(1000))
returns varchar(1000)
as
begin
	declare @RetVal varchar(1000);
	declare @i int;
	set @i =charindex(@Name, @Expression);

	if (@i = 0) 
		set @RetVal = Null
	else
		begin
		set @Expression = substring(@Expression, @i + len(@Name) + 1,1000);
		set @i = charindex(';', @Expression);
		if (@i = 0) 
			set @RetVal = @Expression 
		else 
			set @RetVal = left(@Expression,@i-1)
		end
	set @RetVal = RTRIM(LTRIM(@RetVal));
	if (left(@RetVal,1) = '=') set @RetVal =substring(@RetVal,2,1000);
	return (@RetVal)
end

go

select dbo.GetValue('uid','database=one;uid=hey;pwd=none')
select dbo.GetValue('database','database=one;uid=hey;pwd=none')
select dbo.GetValue('pwd','database=one;uid=hey;pwd=none')
select dbo.GetValue('garbage','database=one;uid=hey;pwd=none')
go

drop function dbo.GetValue

- Jeff

Session101
Starting Member

15 Posts

Posted - 2004-10-07 : 22:56:57

Is there a way to do all the parsing within a select statement?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-07 : 23:48:46

ok... here ya go: 1 select (plus a numbers table)


Declare @myTable table (id int identity(1,1), myVal varchar(1000))
Insert Into @myTable
Select 'name_comment=seasoned;servicer_name_1=WELL'
Union Select 'name_comment=z;servicer_name_1'
Union Select 'name_comment=yes'

Declare @maxLength int
Select @maxLength = max(len(myVal)) From @myTable

Declare @numbers table (n int identity(0,1), blah int)
Insert Into @numbers
Select 0 from master.dbo.syscolumns

	Select
		id, 
		myVal, 
		splitVal, 
		paramName = left(splitVal,isnull(nullif(charindex('=',splitVal)-1,-1),len(splitVal))),
		paramName = case when charindex('=',splitVal)>0 then right(splitVal,len(splitVal)-charindex('=',splitVal)) else null end
	From
		(
		Select
			id,
			myVal,
			posA,
			posB = min(posB),
			splitVal = rtrim(ltrim(substring(myVal,isnull(posA+1,1),isnull(min(posB)-1,len(myVal))-isnull(posA,0))))
		From
			(		
			Select A.id, A.myVal, posA = isnull(B.n,0), posB = C.n
			From @myTable A
			Left Join (Select * From @numbers Where n between 0 and @maxLength) B
			On Substring(A.myVal,B.n,1)=';'
			and len(rtrim(ltrim(A.myVal)))>=B.n
			Left Join (Select * From @numbers Where n between 0 and @maxLength) C
			On B.n+1<=C.n
			and Substring(A.myVal,C.n,1)=';'
			and len(rtrim(ltrim(A.myVal)))>=C.n
			Union
			Select A.id, A.myVal, posA = 0, posB = B.n
			From @myTable A
			Left Join (Select * From @numbers Where n between 0 and @maxLength) B
			On 0<=B.n
			and Substring(A.myVal,B.n,1)=';'
			and len(rtrim(ltrim(A.myVal)))>=B.n
			) Z
		Group by id, myVal, posA
		) A

Corey

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-08 : 09:07:31

good approach Corey -- I think I like that idea better. Break it all up into a recordset and then query as you need.

I think this might be a littler shorter and QA tells me it's more efficient (though it may not be as flexible as yours, I couldn't tell for sure):


select 	case when Split > [End] or SPlit = 0 then substring(Val,Start + 1, [End]-Start-1) else Substring(Val, Start+1, Split-Start-1) end as [Name], 
	case when Split > [End] or Split = 0 then Null else substring(Val, Split+1, [End]-Split-1) end as Value 
from
(
	select  Val, CharIndex(';',Val,N) as Start, CharIndex(';',Val,N+1) as [End], CharIndex('=',Val,N+1) as Split
	from
	(
		select ';' + myVal + ';' as Val
		from @myTable
	) a
	cross join @Numbers
	where
	  N < len(Val) and substring(Val,N,1) = ';'
) a

I think it might be a little easier to follow / alter as needed.

- Jeff

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-08 : 09:17:07

Well mine was a variation of a solution I had just done for something else, and it seemed similar enough to use. You definitely have a simpler version for the task at hand though

Corey

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-10-08 : 20:24:38

Is that your Vette in the photos Corey?

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-08 : 23:01:57

I wish!

Its my uncle's (in-law: my wife's uncle's vette)

We went up to visit last weekend, and his new girlfriend wanted to see it

I have a 94 black mustang gt conv. and my wife has a dodge caravan and we share a 97 camry... mostly poor am I

Corey

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-10-08 : 23:35:36

I used to have this http://www.shsu.edu/~vis_kas/images/jmviper.jpg (that's my wife in the pic) and after my boss crashed it; http://www.shsu.edu/~vis_kas/images/viper21s.JPG Talk about job security ;)

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-09 : 00:59:01

that was a very nice car... sorry to see such a nice car so utterly destroyed

i'd say your boss definitely owes you

Corey

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-10-09 : 01:18:38

It was a bonus his insurance company paid me more than it was worth so I had it for 5 years and broke even.

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources