January 10, 2012 at 8:06 am
HI,
i want a query which give me a second value from comma separated string.
for e.g...
declare @string varchar(100) = 'abcd,efg,hij,klmn,opqrstu,vwxyz'
so now i want query which give me a result base on some condition so i get only one value from string
when i choose second value then efg
when i choose third then hij
......
......
Appreciate your help in advance
Thanks
January 10, 2012 at 8:14 am
To help you better, please provide us table DDLs, sample data & expected output.
January 10, 2012 at 8:16 am
I forgot to mention T-SQL scripts. We will extend your existing code to meet your requirements.
January 10, 2012 at 8:20 am
I can't remember whether or not Jeff Moden's string splitter[/url] already does that, or if I had to edit it but I do know that the TALLY table method he uses numbers each item so it would only take a little effort to convert his code if it doesn't already have the capability.
January 10, 2012 at 9:13 am
Cadavre (1/10/2012)
I can't remember whether or not Jeff Moden's string splitter[/url] already does that, or if I had to edit it but I do know that the TALLY table method he uses numbers each item so it would only take a little effort to convert his code if it doesn't already have the capability.
Right "out of the box" it includes the position. 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 10, 2012 at 9:15 am
Sean Lange (1/10/2012)
Cadavre (1/10/2012)
I can't remember whether or not Jeff Moden's string splitter[/url] already does that, or if I had to edit it but I do know that the TALLY table method he uses numbers each item so it would only take a little effort to convert his code if it doesn't already have the capability.Right "out of the box" it includes the position. 🙂
Good, knew I wasn't imagining it 😀
January 10, 2012 at 9:17 am
Example usage:
select * from dbo.DelimitedSplit8K('abcd,efg,hij,klmn,opqrstu,vwxyz', ',')
where ItemNumber = 3
This will return 3, 'hij'. I think is exactly what the OP was looking for.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 10, 2012 at 8:37 pm
Thank you all of you
sean: can you please elaborate what is dbo.DelimitedSplit8K?
January 10, 2012 at 8:39 pm
Sean: if you are talking about function then its not possible in my case.
i want through query only
January 10, 2012 at 8:59 pm
latitiacasta (1/10/2012)
Thank you all of yousean: can you please elaborate what is dbo.DelimitedSplit8K?
Do a searh of this site for dbo.DelimitedSplit8K. You should find quite a bit of information. But to help out, try reading this article Tally OH! An Improved SQL 8K “CSV Splitter” Function[/url].
January 10, 2012 at 9:20 pm
latitiacasta (1/10/2012)
Sean: if you are talking about function then its not possible in my case.i want through query only
If you have to do this in a single query you are going to need to make yourself really familiar with string manipulation in sql. (charindex, substring, left, right, etc) Even then it will be really challenging because you are going to have nested string functions up to your elbows. 😛
Why are you limited to something like this in a single query? You are going to have to parse your string either in a function or **cough**cursor/while loop**cough** or do really complicated and difficult to maintain/debug string hackery.
Seems to me this sort of parsing you are talking belongs in a stored procedure and said stored procedure calls a string parser.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 10, 2012 at 10:17 pm
latitiacasta (1/10/2012)
Sean: if you are talking about function then its not possible in my case.i want through query only
Declare @Temp table (Rid int identity,String varchar(25))
declare @string varchar(100)
declare @Input int
Select @string = 'abcd,efg,hij,klmn,opqrstu,vwxyz,' /*I have added extra ',' here for my code to run*/
Select @Input = 3 /*Give your desire input here.*/
While LEN(@string)>0
BEGIN
Insert into @Temp
Select SUBSTRING(@string,0,CHARINDEX(',',@string)) aaaaaaaaa
Select @string=SUBSTRING(@string,CHARINDEX(',',@string)+1,LEN(@string))
END
Select * from @Temp Where Rid=@Input
January 10, 2012 at 10:28 pm
latitiacasta (1/10/2012)
Sean: if you are talking about function then its not possible in my case.i want through query only
Why not convert Jeff's function to use APPLY, like this:
SELECT ItemNumber, Item
FROM (SELECT pString = 'abcd,efg,hij,klmn,opqrstu,vwxyz', pDelimiter = ',') d
CROSS APPLY( -- a string-splitter function
SELECT
ItemNumber= CAST(1 AS BIGINT),
Item= CAST(LEFT(d.pString, ISNULL(NULLIF(CHARINDEX(d.pDelimiter, d.pString, 1),0)-1,8000)) AS VARCHAR(8000))
UNION ALL
SELECT
ItemNumber= 1+ROW_NUMBER() OVER(ORDER BY @@SPID),
Item= SUBSTRING(d.pString,n,ISNULL(NULLIF(CHARINDEX(d.pDelimiter,d.pString,n),0)-n,8000))
FROM (
SELECT n = n+1
FROM (
SELECT TOP (ISNULL(DATALENGTH(d.pString),0))
n = (n1 + n2 + n3 + n4)
FROM (((VALUES (0),(100),(200),(300),(400),(500),(600),(700),(800),(900)) t3 (n3)
CROSS JOIN (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t1 (n1))
CROSS JOIN (VALUES (0),(10),(20),(30),(40),(50),(60),(70),(80),(90)) t2 (n2))
CROSS JOIN (VALUES (0),(1000),(2000),(3000),(4000),(5000),(6000),(7000),(8000),(9000)) t4 (n4)
) tally
WHERE SUBSTRING(d.pString,n,1) = d.pDelimiter
) nums
) StringSplitter
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 10, 2012 at 10:31 pm
yuvipoy (1/10/2012)
latitiacasta (1/10/2012)
Sean: if you are talking about function then its not possible in my case.i want through query only
Declare @Temp table (Rid int identity,String varchar(25))
declare @string varchar(100)
declare @Input int
Select @string = 'abcd,efg,hij,klmn,opqrstu,vwxyz,' /*I have added extra ',' here for my code to run*/
Select @Input = 3 /*Give your desire input here.*/
While LEN(@string)>0
BEGIN
Insert into @Temp
Select SUBSTRING(@string,0,CHARINDEX(',',@string)) aaaaaaaaa
Select @string=SUBSTRING(@string,CHARINDEX(',',@string)+1,LEN(@string))
END
Select * from @Temp Where Rid=@Input
Suggest you read the same article I mention above. No need for a while loop.
Edit: Got hit by the quote bug.
January 10, 2012 at 11:12 pm
Cadavre (1/10/2012)
I can't remember whether or not Jeff Moden's string splitter[/url] already does that, or if I had to edit it but I do know that the TALLY table method he uses numbers each item so it would only take a little effort to convert his code if it doesn't already have the capability.
Another vote for using a function here, though if performance and flexibility is important, you might want to choose the CLR version over the T-SQL solution:
Definition:
CREATE ASSEMBLY Split
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300D9240D4F0000000000000000E00002210B010800000E000000060000000000006E2D0000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000182D000053000000004000007003000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000740D000000200000000E000000020000000000000000000000000000200000602E7273726300000070030000004000000004000000100000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001400000000000000000000000000004000004200000000000000000000000000000000502D0000000000004800000002000500E0210000380B000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000008A026F1300000A2D0D026F1400000A0373040000062A168D130000011673040000062A001330020018000000010000110274040000020A03066F080000065404066F0A000006512A1E02281500000A2AD602281500000A02037D0100000402047D0300000402027B010000048E697D0200000402730C0000067D0500000402167D040000042A000013300500C200000002000011027B04000004027B020000043302162A027B040000040A2B56027B010000040693027B030000043342027B05000004256F0800000617586F09000006027B05000004027B01000004027B0400000406027B0400000459731600000A6F0B000006020617587D04000004172A0617580A06027B0200000432A1027B05000004256F0800000617586F09000006027B05000004027B01000004027B04000004027B02000004027B0400000459731600000A6F0B00000602027B020000047D04000004172A1E027B050000042A1A731700000A7A1E027B060000042A2202037D060000042A1E027B070000042A2202037D070000042A1E02281500000A2A0042534A4201000100000000000C00000076322E302E35303732370000000005006C000000C0030000237E00002C0400005C04000023537472696E67730000000088080000080000002355530090080000100000002347554944000000A00800009802000023426C6F6200000000000000020000015717A2030902000000FA253300160000010000001600000004000000070000000C00000009000000010000001800000012000000020000000200000003000000050000000300000001000000020000000200000000000A00010000000000060052004B0006006C0059000A00990084000A001F020402060067024802060091027F020600A8027F020600C5027F020600E4027F020600FD027F02060016037F02060031037F0206004C037F02060065034802060079037F020600B20392030600D20392030A00F003040206001A044B0006001F044B00060026044B0006003E0492030000000001000000000001000100010010001400000005000100010003011000290000000500010004000301100039000000050006000800210049012E0021004F0132002100560135000100600132000100660138000100C00132000100DA0152005020000000009600A2000A0001007420000000009600A800120003009820000000008618B0001B000600A020000000008318B0001F000600D82000000000E101B60026000800A62100000000E109E7002A000800AE2100000000E1011E011B000800B521000000008308940140000800BD21000000008308A10144000800C621000000008308AE0149000900CE21000000008308B7014D000900D721000000008618B0001B000A0000000100FE01000002003102000001003B02020002003F0202000300740200000100FE01000002003102000001007902000001007902030009001100DE002600110012012A00110043011B002100B0001B002900B0001B003100B0004D003900B0004D004100B0004D004900B0004D005100B0004D005900B0004D006100B0004D006900B0004D007100B00070007900B0004D008100B00044008900B0001B009100B0001B00190005042600190010040D020900B0001B00A100B0001702A900B0001B00B100B0001B00200093007500240023005D002E00330028022E00430037022E008B0076022E004B003D022E00530028022E00730037022E003B0037022E0083006D022E005B004C022E0063003702C100C3002302E100C30023020001C30023022001C30023024001C30023026001C300230212021F02030001000400020000006D013C000000F00155000000F901590002000600030001000900050002000800050002000A00070001000B00070003000A00030003000C00050003000E000700048000000100000029115485000000000000A20000000200000000000000000000000100420000000000020000000000000000000000010078000000000003000200040002000000003C4D6F64756C653E0053706C69742E646C6C0055736572446566696E656446756E6374696F6E730053706C6974456E756D657261746F720053706C6974526F77006D73636F726C69620053797374656D004F626A6563740053797374656D2E436F6C6C656374696F6E730049456E756D657261746F720053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C43686172730053706C69740046696C6C526F77002E63746F720053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E4D6F76654E657874004D6F76654E6578740053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E6765745F43757272656E74006765745F43757272656E740053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E526573657400526573657400696E707574006C656E6774680064656C696D69746572007374617274007265636F72640053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E43757272656E74006765745F53657175656E6365007365745F53657175656E6365006765745F4974656D007365745F4974656D003C53657175656E63653E6B5F5F4261636B696E674669656C64003C4974656D3E6B5F5F4261636B696E674669656C640053657175656E6365004974656D00496E707574004D6963726F736F66742E53716C5365727665722E5365727665720053716C46616365744174747269627574650044656C696D69746572006F626A0073657175656E63650053797374656D2E52756E74696D652E496E7465726F705365727669636573004F7574417474726962757465006974656D0076616C75650053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C7475726541747472696275746500436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053716C46756E6374696F6E417474726962757465006765745F49734E756C6C006765745F56616C7565004368617200537472696E67004E6F74496D706C656D656E746564457863657074696F6E00436F6D70696C657247656E6572617465644174747269627574650000000000032000000000003D94CEAD7FD4E84AB312816CB21986F70008B77A5C561934E0890700021209120D03080003011C1008100E03200001062002011D0303032000020320001C03061D03020608020603030612100328001C0320000804200101080320000E042001010E02060E032800080328000E12010001005408074D617853697A65FFFFFFFF04200101028196010006005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000054557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D446174614163636573730000000054020F497344657465726D696E69737469630154020949735072656369736501540E1146696C6C526F774D6574686F644E616D650746696C6C526F77540E0F5461626C65446566696E6974696F6E2173657175656E636520494E542C206974656D204E564152434841522834303030290420001D030407011210072003011D0308080307010804010000000E01000953706C69747465724200000501000000000E0100094D6963726F736F667400002001001B436F7079726967687420C2A9204D6963726F736F6674203230313100000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301000000402D000000000000000000005E2D0000002000000000000000000000000000000000000000000000502D000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500204000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000180300000000000000000000180334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001005485291100000100548529113F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00478020000010053007400720069006E006700460069006C00650049006E0066006F00000054020000010030003000300030003000340062003000000034000A00010043006F006D00700061006E0079004E0061006D006500000000004D006900630072006F0073006F006600740000003C000A000100460069006C0065004400650073006300720069007000740069006F006E0000000000530070006C00690074007400650072004200000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0034003300390033002E00330034003100330032000000000034000A00010049006E007400650072006E0061006C004E0061006D0065000000530070006C00690074002E0064006C006C0000005C001B0001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020004D006900630072006F0073006F006600740020003200300031003100000000003C000A0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530070006C00690074002E0064006C006C00000034000A000100500072006F0064007500630074004E0061006D00650000000000530070006C00690074007400650072004200000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0034003300390033002E00330034003100330032000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0034003300390033002E003300340031003300320000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000703D00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE
GO
CREATE FUNCTION dbo.[Split]
(
@Input nvarchar(max),
@Delimiter nchar(1))
RETURNS TABLE
(
sequence integer NULL,
item nvarchar(4000) NULL
)
WITH EXECUTE AS CALLER
AS EXTERNAL NAME Split.UserDefinedFunctions.SPLIT;
Example:
DECLARE @string varchar(100) =
'abcd,efg,hij,klmn,opqrstu,vwxyz,';
SELECT
ss.item
FROM dbo.Split(@string, ',') AS ss
WHERE
ss.sequence = 2;
Source:
using System.Collections;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[SqlFunction
(
DataAccess = DataAccessKind.None, // No user data access by this function
SystemDataAccess = SystemDataAccessKind.None, // No system data access by this function
IsDeterministic = true, // This function is deterministic
IsPrecise = true, // This function is precise
FillRowMethodName = "FillRow", // The method called by SQL Server to obtain the next row
TableDefinition =
"sequence INT, item NVARCHAR(4000)" // Returned table definition
)
]
public static IEnumerator Split
(
[SqlFacet(MaxSize = -1)] SqlChars Input,
char Delimiter
)
{
return Input.IsNull ?
new SplitEnumerator(new char[0], char.MinValue) :
new SplitEnumerator(Input.Value, Delimiter);
}
sealed class SplitEnumerator : IEnumerator
{
// Constructor (called once when the object is created)
internal SplitEnumerator(char[] Input, char Delimiter)
{
// Save references
input = Input;
delimiter = Delimiter;
// Remember the length of the character array
length = input.Length;
// Structure holding split rows
record = new SplitRow();
// Starting at the first character
start = 0;
}
// Enumerator implementation
#region IEnumerator Methods
bool IEnumerator.MoveNext()
{
// No more rows?
if (start == length) { return false; }
// Find the next delimiter
for (int i = start; i < length; i++)
{
if (input == delimiter)
{
// Increment the sequence number
record.Sequence++;
// Save the split element
record.Item = new string(input, start, i - start);
// Set the next element search start point
start = i + 1;
return true;
}
}
// Last item
record.Sequence++;
record.Item = new string(input, start, length - start);
start = length;
return true;
}
object IEnumerator.Current
{
get { return record; }
}
void IEnumerator.Reset()
{
throw new System.NotImplementedException();
}
#endregion
readonly char[] input; // Reference to the string to be split
readonly int length; // Length of the input string
readonly char delimiter; // The delimiter character
int start; // Current search start position
SplitRow record; // Each row to be returned
}
public static void FillRow(object obj, out int sequence, out string item)
{
var r = (SplitRow)obj;
sequence = r.Sequence;
item = r.Item;
}
sealed class SplitRow
{
internal int Sequence { get; set; } // Sequence of the element
internal string Item { get; set; } // The element
}
};
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply