RE: A quick query puzzle: – SQLServerCentral

SSC Guru

Points: 186127

October 5, 2012 at 4:24 am

S_Kumar_S (10/5/2012)
This is really good but problem is that my table has multiple rows and this is a table function. So it has to be a scalar function for me to use....

Find some good reading material covering functions. You absolutely don't need a function for this - and most folks would recommend a table-valued function over the alternatives. It's far simpler than you think:

SELECT name,

REPLACE(

REPLACE(name,

'012345','xxxxxx'),

'123456','xxxxxx'),

'234567','xxxxxx'),

'345678','xxxxxx'),

'456789','xxxxxx')

FROM t1

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden