Hi, I wanted to be able to parse a string in a column that is delimited by a '' character. Didn't want too much bulk code, and not sure if this is possible in one statement. I've tried CHARINDEX, but maybe there are easier or better ways? It should have this logic: say string = MyGenreMyClassMySuBCategory select X as Position1, Y as Position2, Z as Position3 Postion1 Position2 Position3 -------------------------------- My Genre MyClass MySuBCategory Thx for help in advance!
Use CHARINDEX with SUBSTRING function to get the desired output.. MohammedU. Moderator SQL-Server-Performance.com
you can make use of various string splitting function herehttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648 KH
Thanks khtan, that's exactly what I'm looking for - although I'd like to change the function a little to return it each delimited value as a column (for each record) rather than a table (multiple rows for each value). Can you or anyone give some help on that? I'd like to see: DValue1,DValue2,DValue3,Field1, Field2, (etc....) The function is below. CREATE FUNCTION dbo.Split ( @RowData nvarchar(2000), @SplitOn nvarchar(5) ) RETURNS @RtnValue table ( Id int identity(1,1), Data nvarchar(100) ) AS BEGIN Declare @Cnt int Set @Cnt = 1 While (Charindex(@SplitOn,@RowData)>0) Begin Insert Into @RtnValue (data) Select Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1))) Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData)) Set @Cnt = @Cnt + 1 End Insert Into @RtnValue (data) Select Data = ltrim(rtrim(@RowData)) Return END
"although I'd like to change the function a little to return it each delimited value as a column (for each record) rather than a table (multiple rows for each value)" What do you mean by this ? KH
See if it works for you<img src='/community/emoticons/emotion-1.gif' alt='' /><br /><br />declare @sql Varchar(1000)<br />select @sql = 'MyGenreMyClassMySuBCategory'<br />select @sql = 'select '+char(39)+replace(@sql, '', ''',''')+char(39)<br />exec( @sql)<br /><br /><br />MohammedU.<br />Moderator<br />SQL-Server-Performance.com
Thx, MohammedU - this may work, but can you tell me how to incorporate this into a more complex sql query. What I mean is rather than {select @sql = 'MyGenreMyClassMySuBCategory'} I want to substiture the column name of the table to parse, rather than give it a static value. Will this work? Edit: Actually I tried this as test without the dynamic sql part, and it doesn't give desired result. Although it replaces it into comma delimited, I wanted to represent each part as a column. Thx
Uhoh, that sounds like a case forhttp://www.sommarskog.se/dynamic_sql.html It's a long article, but well worth reading. And while you'Re at Erlands' site, you might also want to check out his article on arrays and lists in SQL. -- Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:http://www.insidesql.de
Ok, ALL - one more bit of help needed: I've chosen this function below, which suits me fine: However I'd like a way to modify it so I can return a null or empty string if the fieldnum I pass in doesn't exist. As it currently stands, it returns an invalid parameter passed error. Some data in the field has 'ac' and others have 'a' only, so if I try to specify field3 when only 'a' exists, I get the error. Any ideas? http://www.mindsdoor.net/SQLTsql/f_GetEntryDelimiitted.html ---------------------------------------------------------------- if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_GetEntryDelimiitted]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[f_GetEntryDelimiitted] GO Create function f_GetEntryDelimiitted ( @line varchar(4000) , @fldnum int , @delim varchar(10) , @quoted varchar(1)-- Y/N ) returns varchar(400) as begin /* create table #a (s varchar(1000)) insert #a select '"John","Smith","IT",2,"sql",1' insert #a select '"Fred","Jones","Assassin",7,"Guns",5' insert #a select '"Peter","Brown","Politics",23,minister,5' select a.* from ( select Forname= dbo.f_GetEntryDelimiitted (s, 1, ',', 'Y') , Surname = dbo.f_GetEntryDelimiitted (s, 2, ',', 'Y') , Profession = dbo.f_GetEntryDelimiitted (s, 3, ',', 'Y') , ProfessionExp = dbo.f_GetEntryDelimiitted (s, 4, ',', 'Y') , Skill = dbo.f_GetEntryDelimiitted (s, 5, ',', 'Y') , SkillExp = dbo.f_GetEntryDelimiitted (s, 6, ',', 'Y') from #a ) a drop table #a */ declare@i int , @j int , @delimUsed varchar(11) , @s varchar(4000) select @i = 1 while@fldnum > 0 begin select @fldnum = @fldnum - 1 if substring(@line, @i, 1) = '"' and @Quoted = 'Y' begin select @delimUsed = '"' + @Delim , @i = @i + 1 end else begin select @delimUsed = @Delim end select @j = charindex(@delimUsed, @line, @i) if @j = 0 select @j = datalength(@line) + 1 if @fldnum > 0 select @i = @j +len(@delimused) end select @s = substring(@line, @i, @j - @i) return@s end go
Oh, Nigel's function. You can change select @s = substring(@line, @i, @j - @i) to select @s = case when @j > @i then substring(@line, @i, @j - @i) else NULL end KH