CODEWITHSUNDEEP
sql-serverstringt-sqlreplace
user6363065
user6363065

Reputation: 81

Change the string in SQL

I have this stored procedure in which I am passing @CourseID as 

|CRS0001095|CRS0001223|CRS0001224|CRS0001225|CRS0001229|CRS0001238|CRS000124

which is from a web application. But I want to replace this string as

'CRS0001095', 'CRS0001223', 'CRS0001224', 'CRS0001225', 'CRS0001229', 'CRS0001238', 'CRS000124'

so that I can pass this string to my query.

I tried Replace but that causes an error. How can I change that string?

ALTER PROCEDURE [dbo].[LSS_e_test_sp]
    @TRANTYPE VARCHAR(30)='',
    @PARAM1 VARCHAR(30)='',
    @PARAM2 VARCHAR(30)='',
    @PARAM3 VARCHAR(30)='',
    @PARAM4 VARCHAR(30)='',
    @PARAM5 VARCHAR(3000)=''
AS
    DECLARE 
        @WORKORDERNO VARCHAR(20),
        @EVENTPOINT VARCHAR(30),
        @LASTEDITBY VARCHAR(30),
        @ERRMSG VARCHAR(500),
        @ProductionLine VARCHAR(100),
        @CourseID VARCHAR(3000),
        @OperatorsTraLevel VARCHAR(30),
        @reqOperatorLevel VARCHAR(30)

    IF @TRANTYPE = 'getOperatorLavel'
    BEGIN
        SET @WORKORDERNO = @PARAM1
        SET @EVENTPOINT = @PARAM2
        SET @LASTEDITBY = @PARAM3
        SET @CourseID = @PARAM5

        Print @WORKORDERNO
        Print @EVENTPOINT
        Print @LASTEDITBY
        Print @CourseID

        select @OperatorsTraLevel = MAX(convert(real, TraingLavel)) 
        from ftx_tm_courselevelMapping 
        where coursenumber in (@CourseID)

        print @OperatorsTraLevel

        RETURN 0
END

Upvotes: 0

Views: 90

Answers (3)

Alex Kudryashev
Alex Kudryashev

Reputation: 9470

If your MS SQL Server is 2016 then you can enjoy string_split function.

...where si in (select value from string_split(@CourseID,'|'))

Else if MS SQL Server is 2008 or newer then you can split the string in the query.

;with dat(x) as (
select cast( '<s>'+replace(@CourseID,'|','</s><s>')+'</s>' as xml)
)
select @OperatorsTraLevel = MAX(convert (real, TraingLavel)) 
from ftx_tm_courselevelMapping cross join dat
where coursenumber in (select t.v.value('.[1]','varchar(20)') from x.nodes('s') t(v))

Upvotes: 0

EoinS
EoinS

Reputation: 5482

Try another route:

WHERE CHARINDEX(coursenumber,@CourseID)>0

Don't even split @CourseID and select data where coursenumber is in your @CourseID string.

Upvotes: 1

Lu&#237;s Tavares
Lu&#237;s Tavares

Reputation: 139

I'm assuming that you are using @CourseID in a that IN Clause, so you can do something like


declare @str nvarchar(max) = '|CRS0001095|CRS0001223|CRS0001224|CRS0001225|CRS0001229|CRS0001238|CRS000124';
declare @ret_list as table ( cr_id nvarchar(max) );
declare @pos int;
declare @cr_id nvarchar(max)

while CHARINDEX('|', @str) > 0
begin
    select @pos = CHARINDEX('|', @str);
    select @cr_id = substring(@str, 1, @pos-1);
    insert into @ret_list (cr_id) values (@cr_id);
    select @str = SUBSTRING(@str, @pos+1, LEN(@str) - @pos)

end
select * from @ret_list where cr_id not like ''

After that, replace the following:

 select @OperatorsTraLevel = MAX(convert (real, TraingLavel)) from ftx_tm_courselevelMapping where coursenumber in (@CourseID)

with 

 select @OperatorsTraLevel = MAX(convert (real, TraingLavel)) from ftx_tm_courselevelMapping where coursenumber in (select cr_id from @ret_list where cr_id not like '')

and you should be golden!

Upvotes: 1

Related Questions