CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
watermelon_heart
watermelon_heart

Reputation: 35

how to modify t-sql to process multiple records not just one.

I am working on a function to remove/ replace special characters from a string from a column named "Title". Currently I am testing the code for one record at a time. I would like to test the code against all the records in the table, but I do not know how to modify the current t-sql to process all the records rather than just one at a time. I would appreciate if someone could show me how, or what type of modifications I need to do to be able to process all records.

This is the code as I have it right now:

DECLARE    @str VARCHAR(400);
DECLARE    @expres VARCHAR(50) = '%[~,@,#,$,%,&,*,(,),.,!,´,:]%'

SET @str = (SELECT REPLACE(REPLACE(LOWER([a].[Title]), CHAR(9), ''), ' ', '_') FROM [dbo].[a] WHERE [a].[ID] = '43948')

WHILE PATINDEX(@expres, @str) > 0

        SET @str = REPLACE(REPLACE(@str, SUBSTRING(@str, PATINDEX(@expres, @str), 1), ''), '-', ' ')
        SELECT     @str COLLATE SQL_Latin1_General_CP1251_CS_AS

For a Title containing the value: Schöne Wiege Meiner Leiden, the output after the code is applied would be: schone_wiege_meiner_leiden

I would like to make the code work to process multiple records rather that one like is done currently by specifying the ID. I want to process a bulks of records.

I hope I can get some help, thank you in advance for your help.

Code example taken from: remove special characters from string in sql server

Upvotes: 0

Views: 70

Answers (1)

Sean Lange
Sean Lange

Reputation: 33571

There is no need for a loop here. You can instead use a tally table and this can become a set based inline table valued function quite easily. Performance wise it will blow the doors off a loop based scalar function.

I keep a tally table as a view in my system. Here is the code for the tally table.

create View [dbo].[cteTally] as

WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    )
select N from cteTally

Now comes the fun part, using this to parse strings and all kinds of various things. It has been dubbed the swiss army knife of t-sql. Anytime you start thinking loop, try to think about using a tally table instead. Here is how this function might look.

create function RemoveValuesFromString
(
    @SearchVal nvarchar(max)
    , @CharsToRemove nvarchar(max)
) returns table as 
    RETURN

    with MyValues as
    (
        select substring(@SearchVal, N, 1) as MyChar
            , t.N
        from cteTally t 
        where N <= len(@SearchVal)
            and charindex(substring(@SearchVal, N, 1), @CharsToRemove) = 0
    )

    select distinct MyResult = STUFF((select MyChar + ''
                    from MyValues mv2
                    order by mv2.N
                    FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'), 1, 0, '')
        from MyValues mv
    ;

Here is an example of how you might be able to use this. I am using a table variable here but this could be any table or whatever.

declare @SomeTable table
(
    SomeTableID int identity primary key clustered
    , SomeString varchar(max)
)

insert @SomeTable
select 'This coffee cost $32.!!! This is a@ tot$@a%l r)*i-p~!`of^%f' union all
select 'This &that'

select *
from @SomeTable st
cross apply dbo.RemoveValuesFromString(st.SomeString, '%[~,@#$%&*()!´:]%`^-') x

Upvotes: 1

Related Questions