How to split a record into multiple rows in SQL Server

Question

I have a column Permission of type varchar. I want to split the data into multiple rows like below... I need help..

Note: remove the dashes between numbers

Answer 1

I did as follows:

DECLARE @TEST TABLE (USERID INT,PERMISSION VARCHAR(MAX))
INSERT INTO @TEST VALUES (1,'1-2-3-4-5-6-7-8-9-10-11-'),(2,'1-2-4-7-8-9')
DECLARE @RESULT TABLE (USERID INT,PERMISSION VARCHAR(MAX))
DECLARE @USERID INT,@PERMISSION VARCHAR(MAX),@PER INT

DECLARE C CURSOR FOR
SELECT USERID,PERMISSION FROM @TEST
OPEN C
FETCH NEXT FROM C INTO @USERID,@PERMISSION
WHILE @@FETCH_STATUS=0
BEGIN

WHILE( CHARINDEX('-',@PERMISSION))>1
BEGIN
SET @PER= (SUBSTRING(@PERMISSION,0,CHARINDEX('-',@PERMISSION)))
SET @PERMISSION=(SUBSTRING(@PERMISSION,CHARINDEX('-',@PERMISSION)+1,LEN(@PERMISSION)))
INSERT INTO @RESULT VALUES (@USERID,CONVERT(INT,@PER))
END

FETCH NEXT FROM C INTO @USERID,@PERMISSION
END
CLOSE C
DEALLOCATE C

SELECT * FROM @RESULT

The result is:

*------*----------------*      
|USERID|    PERMISSION  | 
*------*----------------*        
|1     |     1          |  
*------*----------------*       
|1     |     2          |  
*------*----------------*       
|1     |     3          | 
*------*----------------*        
|1     |     4          | 
*------*----------------*        
|1     |     5          | 
*------*----------------*        
|1     |     6          | 
*------*----------------*        
|1     |     7          | 
*------*----------------*        
|1     |     8          | 
*------*----------------*        
|1     |     9          |  
*------*----------------*       
|1     |     10         |
*------*----------------*         
|1     |     11         | 
*------*----------------*        
|2     |     1          | 
*------*----------------*        
|2     |     2          |
*------*----------------*         
|2     |     4          | 
*------*----------------*        
|2     |     7          |
*------*----------------*         
|2     |     8          |
*------*----------------*         

Answer 2

Use STRING_SPLIT in sql like this :

SELECT value FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ');

It returns following result table:

value
---------
Lorem
ipsum
dolor
sit
amet.

Look at here for more information

in sql server 2008 you have to create a function like below (refrence) :

CREATE FUNCTION [dbo].[fnSplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
) 
BEGIN 

    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 

    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1

        INSERT INTO @output (splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)

    END 

    RETURN 

END

so excute it like this

select *from dbo.fnSplitString('Querying SQL Server','')