question

sriman avatar image
sriman asked

replace function

With In a string, how to replace all characters with '*' excluding '-' character. let us suppose string a= '123-456-7890' we need to replace all the first 6 numbers with '*' so that the result will be '\*\*\*-\*\*\*-7890' We can handle this by stuff() function. but is there any way to replace all first 6 numeric values to '*' using the wild card characters and ^ symbol just like we use in the string comparison in Like statement str like '%[^[0-9]]%' something like this as we use in like statement Select replace('123-456-7890',[^'-'],'*') or select replace('123-456-6789',^char(45),'*') Thanks Sriman
sql-server-2008sql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
More or less the exact same question was asked and discussed some nine months ago. **[ http://ask.sqlservercentral.com/questions/5386/help-using-replace-with-wildcard-matching-pattern][1]** And here's a very, very basic replace function I wrote, based on Kev Rileys suggestion to use a Tally table (as Kev suggested in his answer to the above linked question). if object_id('Tally') is not null drop table Tally GO select top 20000 identity(int,1,1) as N into Tally FROM master.sys.columns c1, master.sys.columns c2 ALTER TABLE Tally ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR=100 GO if object_id('dbo.fn_Replace') is not null drop function dbo.fn_replace GO CREATE FUNCTION dbo.fn_Replace(@s varchar(100),@pattern varchar(100), @replacewith varchar(1)) RETURNS varchar(100) AS BEGIN RETURN (SELECT TOP(LEN(@s)) CASE WHEN SUBSTRING(@s,N,1) LIKE @pattern THEN @replacewith ELSE SUBSTRING(@s,N,1) END FROM Tally ORDER BY N FOR XML PATH('')) END GO select dbo.fn_Replace('123-ÖÅÄ-7890','[^-]','*') [1]: http://ask.sqlservercentral.com/questions/5386/help-using-replace-with-wildcard-matching-pattern
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sweet. +1 Note to self: Learn how to use tally tables!
0 Likes 0 ·
malpashaa avatar image
malpashaa answered
Try this:
--'1-2-3-4-5-6-7-8-9-0' to be considered the worst prefix.
WITH NumbersCTE AS
(
   SELECT T.number
     FROM (VALUES(01), (02), (03), (04), (05), (06),
                 (07), (08), (09), (10), (11)) AS T(number)
)
--'1-2-3-4-5-6-7-8-9-0' to be considered the worst prefix.
, PossibleLengthCTE AS
(
   SELECT T.length
     FROM (VALUES(6), (7), (8), (9), (10), (11)) AS T(length)
)
SELECT T1.string,
       T3.fixed_string + T2.string2 AS fixed_string
  FROM (VALUES('123-456-7890'),
              ('1-2-3-4-5-6-7-8-9-0')) AS T1(string)
       --TestTable AS T1
       CROSS APPLY
       (SELECT MIN(PL.length) AS length,
               SUBSTRING(T1.string, 1, MIN(PL.length)) AS string1,
               SUBSTRING(T1.string, MIN(PL.length) + 1, 2147483647) AS string2
          FROM PossibleLengthCTE AS PL
         WHERE LEN(REPLACE(SUBSTRING(T1.string, 1, PL.length), '-', '')) = 6) AS T2
       CROSS APPLY
       (SELECT MAX(CASE WHEN T3.number = 01 THEN T3.fixed_char ELSE '' END) +
               MAX(CASE WHEN T3.number = 02 THEN T3.fixed_char ELSE '' END) +
               MAX(CASE WHEN T3.number = 03 THEN T3.fixed_char ELSE '' END) +
               MAX(CASE WHEN T3.number = 04 THEN T3.fixed_char ELSE '' END) +
               MAX(CASE WHEN T3.number = 05 THEN T3.fixed_char ELSE '' END) +
               MAX(CASE WHEN T3.number = 06 THEN T3.fixed_char ELSE '' END) +
               MAX(CASE WHEN T3.number = 07 THEN T3.fixed_char ELSE '' END) +
               MAX(CASE WHEN T3.number = 08 THEN T3.fixed_char ELSE '' END) +
               MAX(CASE WHEN T3.number = 09 THEN T3.fixed_char ELSE '' END) +
               MAX(CASE WHEN T3.number = 10 THEN T3.fixed_char ELSE '' END) +
               MAX(CASE WHEN T3.number = 11 THEN T3.fixed_char ELSE '' END) AS fixed_string
          FROM (SELECT N.number,
                       CASE WHEN SUBSTRING(T2.string1, N.number, 1) = '-'
                                 THEN '-'
                            ELSE '*'
                       END AS fixed_char
                  FROM NumbersCTE AS N
                 WHERE N.number <= T2.length) AS T3(number, fixed_char))AS T3(fixed_string);

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.