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 |
What is difference between group by and partition by?
What is sorting in sql?
Can we insert data in view?
What are the types of variables use in pl sql?
How do I run sql profiler?
What is cursor in pl sql with examples?
Which data dictionary views have the information on the triggers that are available in the database?
Which is better trigger or stored procedure?
What do you know by pl/sql cursors?
How do we tune the code?
What are joins in sql?
Can we enter data in a table in design view?