SQL Server: replace non-char characters in a string without using a temporary table

Question

I have an NVARCHAR(10) column in a table. It can store any UNICODE string.

I want to replace every char that differs from "1" with "0".

Say I have the line '012345C18 *'. I have to get "0100000100".

I managed to do this using an auxiliary table that contains indices from 1 to the size of my column (10), for example:

 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 better? This seems ugly for a simple update, ignoring the fact that the size of the column may change over time. It should also work on SQL> = 2005 .

SQL feed here

Thanks!

Answer 1

Here is a way to do this with cte. On my system, I actually have ctes as the name cteTally. This method generates a view of 10,000 rows with zero readings .;) Your published code works quite well. In this example, I moved the row to the table, as this is what you are working with in a 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, tN, 1) AS Ch FROM @myStrings A CROSS JOIN cteTally t where tN < 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

Answer 3

If you want to update the whole table, UDF may 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 

Using:

 Update aTable Set aField = dbo.F_MakeBinary(aField)