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!

Can you only do this with a standard proc or can you use a .Net procedure? Is this something that needs to run many times or just once to scrub data? — Maurice Reeves, Oct 17 '14 at 15:18
@MauriceReeves It has to run only once (some corrupt data in a couple of our databases) against dozens of big tables(10K+ rows). A simple console app would have solved this, but I asked in case same situation happens again + I don't like that temp table. — darkdante, Oct 17 '14 at 15:40
A numbers table is very useful. Many have it as a permanent table. — ypercubeᵀᴹ, Oct 17 '14 at 16:05

score 7 · Answer 1 · answered Oct 17 '14 at 16:04

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

score 5 · Accepted Answer · answered Oct 17 '14 at 15:45

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,'')

I had to modify my cte a bit to work with sql 2005. In 2008+ you can change that to values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1) instead of all the selects. :D — Sean Lange, Oct 17 '14 at 15:47
I've just created my numbers view with your technique!Thanks:) — darkdante, Oct 17 '14 at 16:45
Can you rely on the order of rows in the subquery without an explicit ORDER BY? — Andriy M, Oct 22 '14 at 11:37
Sorry, there are many subqueries here, of course. I meant the FOR XML subquery. You are concatenating characters with it presuming that they will be in the correct order, and yet you are not using an ORDER BY. — Andriy M, Oct 22 '14 at 11:51

bummi · Answer 3 · 2014-10-17T17:18:35.620

1

If you want to update a whole table a UDF might be useful.

Create FUNCTION dbo.F_MakeBinary(@Param NVarchar(max))
RETURNS NVarchar (max)
AS
BEGIN     
 DECLARE @a NVarchar(max)
 Set @a=@Param
 While PATINDEX(N'%[^0-1]%', @a) > 0
 begin    
    select @a=STUFF(@a, PATINDEX(N'%[^0-1]%', @a),1,'0') 
 end
 Return @a
END

Usage:

Update aTable Set aField = dbo.F_MakeBinary(aField)

edited Oct 17 '14 at 17:18

answered Oct 17 '14 at 15:57

bummi

26,435
13
58
97

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

3 Answers3