August 12, 2012 at 6:21 pm
Two questions.
#1 How can I select without using the LIKE?
#2 How can I get this type of output?
Thanks
101 1
101 2
101 3
101 4
101 5
102 1
102 5
103 1
103 6
104 7
104 8
104 9
CREATE TABLE #Comma
(AccountId int, Commas varchar(100))
INSERT INTO #Comma
(AccountId, Commas)
SELECT 101, '1, 2, 3, 4, 5' UNION ALL
SELECT 102, '1, 5' UNION ALL
SELECT 103, '1, 6' UNION ALL
SELECT 104, '7, 8, 9'
SELECT AccountId
FROM #Comma
WHERE Commas LIKE '%5%'
August 12, 2012 at 8:38 pm
pls try below code
SELECT AccountId
FROM #Comma
WHERE PATINDEX('%5%',Commas)<>0
August 12, 2012 at 8:57 pm
What you need is a split function. The function that turns string of deliminted values into table. There are tons of info on the internet and this site.
Here is one:
http://www.sqlservercentral.com/articles/Tally+Table/72993/
--Vadim R.
August 12, 2012 at 9:01 pm
subbareddy542 (8/12/2012)
pls try below codeSELECT AccountId
FROM #Comma
WHERE PATINDEX('%5%',Commas)<>0
This won't produce the desired output.
--Vadim R.
August 12, 2012 at 11:36 pm
FIRST CREATE ONE FUNCTION.
CREATE FUNCTION SPLIT(@VAL VARCHAR(MAX))
RETURNS @T1 TABLE(COL1 VARCHAR(MAX))
AS
BEGIN
WHILE CHARINDEX(',',@VAL)>0
BEGIN
INSERT INTO @T1 VALUES(SUBSTRING(@VAL,1,(CHARINDEX(',',@VAL))-1))
SET @val=SUBSTRING(@VAL,(CHARINDEX(',',@VAL))+1,LEN(@VAL))
END
INSERT INTO @T1 VALUES(@VAL)
RETURN
END
AFTER WRITE BELOW CODE.
select C.AccountId,SPLIT.COL1 from #Comma c
cross apply SPLIT(C.Commas)
August 13, 2012 at 2:27 am
subbareddy542 (8/12/2012)
FIRST CREATE ONE FUNCTION.CREATE FUNCTION SPLIT(@VAL VARCHAR(MAX))
RETURNS @T1 TABLE(COL1 VARCHAR(MAX))
AS
BEGIN
WHILE CHARINDEX(',',@VAL)>0
BEGIN
INSERT INTO @T1 VALUES(SUBSTRING(@VAL,1,(CHARINDEX(',',@VAL))-1))
SET @val=SUBSTRING(@VAL,(CHARINDEX(',',@VAL))+1,LEN(@VAL))
END
INSERT INTO @T1 VALUES(@VAL)
RETURN
END
AFTER WRITE BELOW CODE.
select C.AccountId,SPLIT.COL1 from #Comma c
cross apply SPLIT(C.Commas)
Sure this split function works. However, I would use a more efficient algorithem if possible. Jeff Moden's splitter (the one suggested above by rVadim) is much more efficient than a while loop.
-- Gianluca Sartori
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply