need to split a string into seperate values.
eg.
col1 col2
----------
100 - 'a,b,c'
200 - 'a,x,b,d,e'
300 - 'c'
result:
value count
-------------
a - 2
b - 1
c - 2
etc.
Answer / gopi muluka
DECLARE @STR VARCHAR(50),
@Char VARCHAR(10),@N INT, @CNT INT
DECLARE @TAB TABLE (VAL VARCHAR(30), CNT INT)
SET @STR='A,B,C,D,E,F,C,A,S,K,C,B'
SET @CHAR=''
SET @N=1
SET @CNT=1
WHILE @N>0
BEGIN
PRINT @STR
SET @CHAR=SUBSTRING(@STR,1,CHARINDEX(',',@STR)-1)
IF NOT EXISTS(SELECT 1 FROM @TAB WHERE VAL=@CHAR)
INSERT @TAB VALUES (@CHAR,@CNT)
SET @STR=SUBSTRING(@STR,CHARINDEX(',',@STR)+1,115)
IF CHARINDEX(@CHAR,@STR)>0
BEGIN
UPDATE @TAB
SET CNT=CNT+1
WHERE VAL=@CHAR
END
SET @N=CHARINDEX(',',@STR)
PRINT @N
END
SELECT * FROM @TAB
Is This Answer Correct ? | 0 Yes | 0 No |
With out using count() function. How to the find total number of rows in a table?
What is meant by <> in sql?
explain about mysql and its features. : Sql dba
How many types of triggers are there in pl sql?
Why do we need view in sql?
what is the purpose of update command in oracle?
What is the Diff b/w Constraints and Trigeer
What is sql dialect?
What is pessimistic concurrency control? : Transact sql
what is the difference difference between procedure and packages
2 Answers 3i Infotech, Oracle,
What is use of trigger?
which types of join is used in sql widely? : Sql dba