SQL Server: Replace characters different than a char in a string using no temporary table

Question

I have a NVARCHAR(10) column in a table. It can store any type of UNICODE strings.

I want to replace every char which is different than '1' with '0'.

Let's say I have the string '012345C18*'. I should get '0100000100'.

I managed to do it using a helper table which contains indexes from 1 to the size of my column (10), like this:

CREATE TABLE HELP(Idx INT)
INSERT INTO HELP 
    SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT      7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10

DECLARE @myStr VARCHAR(10)
SET @myStr = '012345C18*'

SELECT STUFF((SELECT '' + CASE(B.Ch) WHEN '1' THEN '1' ELSE '0' END FROM (
  SELECT SUBSTRING(A.Val,H.Idx,1) AS Ch 
  FROM 
  (SELECT @myStr AS Val) A 
  CROSS JOIN HELP H
)B FOR XML PATH('')),1,0,'')

It works, but can it be done in a nicer way? This seems ugly for a simple update, ignoring the fact that the size of the column can change over time. It also has to run on SQL >=2005.

SQL Fiddle here

Thanks!

Answer 1

Here is a way to do this with a cte. In my system I actually have the ctes as a view name cteTally. This technique generates a 10,000 row view with zero reads. ;) Your code as posted works quite well. For this example I moved the string into a table since that is what you are working with in the real system.

declare @myStrings table(MyVal varchar(10));

insert @myStrings
select '012345C18*';

WITH
    E1(N) AS (select 1 from 
    (
        select (1) union all 
        select (1) union all 
        select (1) union all 
        select (1) union all 
        select (1) union all 
        select (1) union all
        select (1) union all
        select (1) union all
        select (1) union all
        select (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 STUFF((SELECT '' + CASE(B.Ch) WHEN '1' THEN '1' ELSE '0' END FROM (
  SELECT SUBSTRING(A.MyVal, t.N, 1) AS Ch 
  FROM 
  @myStrings A 
  CROSS JOIN cteTally t
  where t.N < LEN(a.MyVal)
)B FOR XML PATH('')),1,0,'')

Answer 2

A slightly different approach, using a recursive query:

WITH cte AS
  ( SELECT v,  i = 0, 
        nv = CAST('' AS NVARCHAR(10))
    FROM t
  UNION ALL
    SELECT v, i+1, 
        CAST(nv + CASE WHEN SUBSTRING(v, i+1, 1) = '1' THEN '1' ELSE '0' END 
          AS NVARCHAR(10))
    FROM cte
    WHERE i+1 <= LEN(v) 
  ) 
SELECT v, nv
FROM cte 
WHERE i = LEN(v) ;

Tested in SQLFiddle