How to split string with delimiter and convert to integer

Question

hi i am writing query in sql server procedure in which i pass comma seprated nvarchar values i want to filter these value in sql server query code is given below

declare @RoleId NVARCHAR(MAX) = '1,2,3'
Declare @RoleTempId  int;
Set @RoleTempId = Cast(@RoleId As INT);
BEGIN
SELECT        dbo.RolePermission.PermissionId, dbo.Permission.PermissionName
FROM            dbo.RolePermission INNER JOIN
                         dbo.Permission ON dbo.RolePermission.PermissionId = dbo.Permission.PermissionId
WHERE        (dbo.RolePermission.RoleId IN (@RoleTempId))
END

this is giving error please guide me about this query thank you.

refer this,http://stackoverflow.com/questions/2647/split-string-in-sql — KumarHarsh, Feb 22 '14 at 13:54

Bogdan Sahlean · Accepted Answer · 2014-02-22T14:09:27.437

2

One simple solution for string splitting is to convert the source (1,2,3) string (which could be declared as VARCHAR and not NVARCHAR if it contains only integers) to XML (<r>1</r><r>2</r><r>3</r>) and then you could shredd this XML and insert every values (integer) into a table variable:

DECLARE @RoleId NVARCHAR(MAX) = '1,2,3'

DECLARE @SelectedRoles TABLE (RoleId INT PRIMARY KEY); -- No duplicates allowed

DECLARE @x XML;
SET @x = N'<r>' + REPLACE((SELECT @RoleId FOR XML PATH('')), N',', N'</r><r>') + N'</r>';
/*
@x content:
<r>1</r><r>2</r><r>3</r>
*/
INSERT  @SelectedRoles (RoleId)
SELECT  y.XmlCol.value('(.)', 'INT') AS RoleId
FROM    @x.nodes('/r') y(XmlCol);

...
WHERE        (dbo.RolePermission.RoleId IN (SELECT sr.RoleId FROM @SelectedRoles sr))

edited Feb 22 '14 at 14:09

answered Feb 22 '14 at 14:04

Bogdan Sahlean

19,233
3
42
57

this gives me duplicate permissions how i solve this @Bogdan Sahlean – Ammar Feb 22 '14 at 14:26
@Ammar: [1] You have to avoid duplicated roles: `1,2,3,3` -> `1,2,3` or [2] you could remove the `PRIMARY KEY` constraint: `TABLE (RoleId INT)` – Bogdan Sahlean Feb 22 '14 at 14:28
Hi, I tried this, my @SelectedRoles table only contains the '1'. Not the rest of the roles – jaredlee.exe Dec 15 '15 at 08:43
1

@jaredlee.exe Check if the separator is `,` – Bogdan Sahlean Dec 15 '15 at 19:35

How to split string with delimiter and convert to integer

1 Answers1