none
Simpler way to write multiple REPLACEs?

    Question

  • A table has a column with data possibly containing a period, comma, / or \ or a space. I want to replace all occurrences of those characters by an empty string.  I can do this by repeatedly applying REPLACE but wonder if there is a smarter was to accomplish this.

    TIA,

    edm2

    Friday, November 08, 2013 1:50 AM

Answers

  • 1. You can use REPLACE on the earlier REPLACE like this

    declare @T as nvarchar(100) = 'jfka/hfs/hjkhas,vf\jgsfgg'
    select REPLACE(REPLACE(REPLACE(@T,'/',' '),'\',' '),',','')

    2. you can use Regular Expressions (CLR), probably the best way

    http://msdn.microsoft.com/en-us/magazine/cc163473.aspx

    http://www.codeproject.com/Articles/42764/Regular-Expressions-in-MS-SQL-Server-2005-2008

     

    [Personal Site] [Blog] [Facebook]signature

    Friday, November 08, 2013 3:25 AM
  • One method is as follows:

    DECLARE @Sample TABLE(string VARCHAR(50));
    INSERT INTO @Sample
    SELECT 'hhjjj123,45/hhh\kk' UNION ALL
    SELECT '-7;655' UNION ALL
    SELECT 'asd5-5dffgdf.!@##$%$&^&*(g105';
    WITH Num1 (n) AS (SELECT 1 UNION ALL SELECT 1),
    Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
    Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y),
    Num4 (n) AS (SELECT 1 FROM Num3 AS X, Num3 AS Y),
    Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num4),
    FinalOutput AS (
    SELECT string, (SELECT CASE WHEN SUBSTRING(string, n, 1) NOT LIKE '[^a-zA-Z0-9]'
                           THEN SUBSTRING(string, n, 1)
                          ELSE ''
                      END + ''
               FROM Nums
               WHERE n <= LEN(string)
               FOR XML PATH('')) AS stringout
    FROM @Sample)
    SELECT string, stringout FROM FinalOutput;


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by edm2 Friday, November 08, 2013 5:56 AM
    Friday, November 08, 2013 4:39 AM

All replies

  • 1. You can use REPLACE on the earlier REPLACE like this

    declare @T as nvarchar(100) = 'jfka/hfs/hjkhas,vf\jgsfgg'
    select REPLACE(REPLACE(REPLACE(@T,'/',' '),'\',' '),',','')

    2. you can use Regular Expressions (CLR), probably the best way

    http://msdn.microsoft.com/en-us/magazine/cc163473.aspx

    http://www.codeproject.com/Articles/42764/Regular-Expressions-in-MS-SQL-Server-2005-2008

     

    [Personal Site] [Blog] [Facebook]signature

    Friday, November 08, 2013 3:25 AM
  • One method is as follows:

    DECLARE @Sample TABLE(string VARCHAR(50));
    INSERT INTO @Sample
    SELECT 'hhjjj123,45/hhh\kk' UNION ALL
    SELECT '-7;655' UNION ALL
    SELECT 'asd5-5dffgdf.!@##$%$&^&*(g105';
    WITH Num1 (n) AS (SELECT 1 UNION ALL SELECT 1),
    Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
    Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y),
    Num4 (n) AS (SELECT 1 FROM Num3 AS X, Num3 AS Y),
    Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num4),
    FinalOutput AS (
    SELECT string, (SELECT CASE WHEN SUBSTRING(string, n, 1) NOT LIKE '[^a-zA-Z0-9]'
                           THEN SUBSTRING(string, n, 1)
                          ELSE ''
                      END + ''
               FROM Nums
               WHERE n <= LEN(string)
               FOR XML PATH('')) AS stringout
    FROM @Sample)
    SELECT string, stringout FROM FinalOutput;


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by edm2 Friday, November 08, 2013 5:56 AM
    Friday, November 08, 2013 4:39 AM