Process and format all records from table, for an specific column

• itortu

  SSCommitted

  Points: 1914

  More actions

  September 6, 2017 at 2:42 pm

  #323481

  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 only need to make the code work to process multiple records rather that one by specifying the ID.

  I hope I can get some help, I am really excited to learn how to make this change.

  Thank you for your help.

  ---

  Code sample originally taken from: remove-special-characters-from-string

• aaron.reese

  SSChampion

  Points: 13498

  More actions

  September 7, 2017 at 10:57 am

  #1958612

  you could just do it with nested REPLACE() functions.  I have only done the first two ([ and @). You would need to be careful with the % as that is a wild card and you might need to escape it first. Yo can then drop the WHERE clause 

  SELECT        REPLACE
                (REPLACE
                    (REPLACE
                        (REPLACE
                            (LOWER([a].[title]), CHAR(9), ''), ' ', '_'
                        ),'[','_'
                    ),'@','_'
                )
FROM [dbo].[a]

  If you don't know what the REGEX is going to contain then I think you would need to build up the string dynamically using the technique you first identified to build the string in the format above and then 

  sp_sqlexec @STR
to execute it.  Note that if you do this the query optimiser will never be able to cache the plan.[/code] to execute it.  Note that if you do this the query optimiser will never be able to cache the plan.
  You could build the above as a function and then pass the value in to the function but this will take a performance hit as it will have to run the function for each row in the results set and again can't be optimised.

  Overall it is a bit ugly.