Find a Integer from a nvarchar(max) / string in SQL

Wednesday, July 25, 2012

Find a Integer from a nvarchar (string) fild ::
In this procedure firstly i create a table ( containing Int value) , from a comma delimited string(nvarchar).
Here you can use like function but there is a problem , suppose according to bellow procedure you are find
"id" whose value is "56". and the string(nvarchar) value is "2,45,212,156,178".
here if you use " like " operator, following select statement reture true as because of 156 in that string(nvarchar) .
For avoiding this problem, i solved that issue by the following SP & Function :


ALTER procedure [dbo].[AbhisHiFi] --'amrita_agp'
(@uNAME nvarchar(20))
as
begin
-- for get the string 
declare @a nvarchar(max)=(select menu_list1 from menu_permit where UserName=@uNAME)

select * from  menu where status=1 and id not in (465) and Url is null
union
-- find "id" within that string(nvarchar) ,here that nvarchar was modified to a table by 
-- a user define function
select * from  menu where status=1 and id in (SELECT ID FROM [dbo].[Abhi_StringToIntList](@a,',')) order by pos

end

AND The  Function is as follows :


ALTER FUNCTION [dbo].[Abhi_StringToIntList]
(@str VARCHAR (MAX), @delimeter CHAR (1))
RETURNS 
    @result TABLE (
        [ID] INT NULL)
AS
BEGIN
-- here the function is return a table , and the table is created through XML
    DECLARE @x XML 
    SET @x = '<t>' + REPLACE(@str, @delimeter, '</t><t>') + '</t>'

    INSERT INTO @result
    SELECT DISTINCT x.i.value('.', 'int') AS token
    FROM @x.nodes('//t') x(i)
    ORDER BY 1

RETURN
END

0 comments:

Post a Comment