Help with String function - 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)

Help with String function

Author

Topic

dukey07
Starting Member

16 Posts

Posted - 2005-10-17 : 17:31:54

I need to parse the following set of strings

Declare @str1 = vachar(500)
Declare @str2 = vachar(500)

Set @str1 = '34;340:59"ImportantTextHere"a5;456T:"MoreStuff"asdfe:29;"3"hy:34;'

Set @str2 = '34;340:59"23"a5;"Hello"456T:"World"asdf"again"e:29;"32"hy:34;'

After Parsing @str1 should return ImportantTextHere,MoreStuff,3
After Parsing @str2 should return 23,Hello,World,again,32

Basically I need to replace everything not in between the quotation marks with a comma or any seperator for that matter. The relevant points are that the data outside of the quotation marks varies, and the ammount of quoted areas within the string can vary. It feels like a job for coalesce and replace but I just am not seeing it.

any ideas?

Thanks, Dukey

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-10-17 : 18:26:49

one quick and dirty way

set nocount on


Declare @str1 varchar(500),
	@str2 varchar(500),
	@str_out varchar(500)

Set @str1 = '34;340:59"ImportantTextHere"a5;456T:"MoreStuff"asdfe:29;"3"hy:34;'

while patindex('%"_"%', @str1) <> 0
begin
	set @str_out = isnull(@str_out,'') + substring(@str1, charindex('"', @str1) + 1, charindex('"', @str1, charindex('"', @str1)+1) - charindex('"', @str1) - 1) + ','
	set @str1 = stuff(@str1, 1, charindex('"', @str1, charindex('"', @str1)+1), '')
end
set @str_out = Substring(@str_out, 1, datalength(@str_out) - 1)

select @str_out

Nathan Skerl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources