## A quick query puzzle:

 Author Message S_Kumar_S SSCarpal Tunnel Group: General Forum Members Points: 4811 Visits: 1101 HiI have a table with below data. Requirement is to replace all integers with continous 6 or more occurances with 'x'. Less than 6 occurances should not be replaced.create table t1(name varchar (100))GOINsert into t1select '1234ABC123456XYZ1234567890ADS'GOINsert into t1select 'cbv736456XYZ543534534545XLS'GOEXPECTED RESULT:1234ABCxxxxxxXYZxxxxxxxxxxADScbvxxxxxxXYZxxxxxxxxxxxxXLSdrop table t1 Online Trainer For SQL DBA and Developer @RedBushTechnologies with 16 yrs exp. mickyT SSCertifiable Group: General Forum Members Points: 5768 Visits: 3320 HiYou could try the following`CREATE FUNCTION replace6plusint(@s1 varchar(100)) RETURNS TABLE AS RETURN WITH firstRun AS ( SELECT replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(@s1,'0','^'),'1','^'),'2','^'),'3','^'),'4','^'),'5','^'),'6','^'),'7','^'),'8','^'),'9','^'),'^^^^^^','------') a UNION ALL SELECT replace(a,'-^','--') FROM firstRun WHERE a like '%-^%' ), secondRun AS ( SELECT replace(a,'-','x') b, charindex('^', a) p FROM firstRun WHERE a not like '%-^%' UNION ALL SELECT stuff(b, p, 1, substring(@s1,p,1)), charindex('^', b) FROM secondRun WHERE p <> 0 ) SELECT b result FROM secondRun WHERE p = 0` S_Kumar_S SSCarpal Tunnel Group: General Forum Members Points: 4811 Visits: 1101 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.... Online Trainer For SQL DBA and Developer @RedBushTechnologies with 16 yrs exp. ChrisM@Work SSC Guru Group: General Forum Members Points: 96498 Visits: 20676 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( REPLACE( 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 ShawFor 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 ModenExploring Recursive CTEs by Example Dwain Camps S_Kumar_S SSCarpal Tunnel Group: General Forum Members Points: 4811 Visits: 1101 Hi Chris.the numbers are not consecutive as you have assumed. And they may be of length greater than 6. I think it is not so straight as it looks. Online Trainer For SQL DBA and Developer @RedBushTechnologies with 16 yrs exp. ben.brugman SSCertifiable Group: General Forum Members Points: 6052 Visits: 2452 With a large amount of replaces this can be done.Problem is that the number of replaces is very large.So I'll show the concept but not the code, because the code would be large.First of all I assum that there are 'strings' which do not occure in the supplied string.Here I'll use the strings '^' and 'x', but if these occure replace then with strings which do not occure.First replace alle the numbers in the strings with1 ChrisM@Work SSC Guru Group: General Forum Members Points: 96498 Visits: 20676 S_Kumar_S (10/5/2012)Hi Chris.the numbers are not consecutive as you have assumed. And they may be of length greater than 6. I think it is not so straight as it looks.Ah, ok. `SELECT t.name, x.New_Name FROM #t1 tCROSS APPLY ( SELECT New_Name = (SELECT Newletter + '' FROM ( SELECT n, Newletter = CASE WHEN d.grouper IS NOT NULL AND COUNT(*) OVER (PARTITION BY d.grouper) > 5 THEN 'x' ELSE letter END FROM ( SELECT n, letter, grouper = ROW_NUMBER() OVER(PARTITION BY t.name ORDER BY ISNUMERIC(letter), n) - N FROM (SELECT TOP(LEN(t.name)) n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM sys.columns a, sys.columns b) tally CROSS APPLY (SELECT letter = SUBSTRING(t.name,n,1)) l ) d ) stri ORDER BY n FOR XML PATH('') , TYPE).value('.', 'varchar(max)')) xORDER BY t.name` “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 ModenExploring Recursive CTEs by Example Dwain Camps ben.brugman SSCertifiable Group: General Forum Members Points: 6052 Visits: 2452 With a large amount of replaces this can be done.Problem is that the number of replaces is very large.So I'll show the concept but not the code, because the code would be large.First of all I assume that there are 'strings' which do not occure in the supplied string.Here I'll use the strings '^' and 'x', but if these occure replace then with strings which do not occure.First replace alle the numbers in the strings with1 becomes ^1 (do this for all 10 numeric characters)^1^ becomes ^^1 (do this for all 10 numeric characters and repeat this large number of times).All number strings have now the shape ^^^^1234 X^^^^^^ becomes x^^^^^EDIT: ABOVE LINE SHOULD BE (sorry)^^^^^^ becomes x^^^^^ x^ becomes xx (repeat a number of times)Now the number strings all have the shape ^^^^1234 or xxxxxxxx12345678x1 becomes x (do this for all 10 numeric characters and repeat this a large number of) times).^ becomes '' (remove all the ^)The number of replaces is large, but maybe could be done in a loops.Or if the database is very large this can be generated.If the database is large and the numbers are very long you need an awfull lot of replaces.For large numbers and large number of rows this might not be a handy method.An alternate solution might be to copy the table change all numbers into '1' do the above with far less substitutes and than replace the 1111 string with the original numbers. With some masking this is possible.thanks for sharing your problem,Ben Brugman S_Kumar_S SSCarpal Tunnel Group: General Forum Members Points: 4811 Visits: 1101 This is quite impressive but it doesn't work for some scenarios. e.g. this one :insert into t1select 'AAA22222222222222BB5BB 33CCCCCCCCC342234234'EDIT: I meant it for Chris response:SELECT t.name, x.New_Name FROM #t1 tCROSS APPLY ( SELECT New_Name = (SELECT Newletter + '' FROM ( SELECT n, Newletter = CASE WHEN d.grouper IS NOT NULL AND COUNT(*) OVER (PARTITION BY d.grouper) > 5 THEN 'x' ELSE letter END FROM ( SELECT n, letter, grouper = ROW_NUMBER() OVER(PARTITION BY t.name ORDER BY ISNUMERIC(letter), n) - N FROM (SELECT TOP(LEN(t.name)) n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM sys.columns a, sys.columns b) tally CROSS APPLY (SELECT letter = SUBSTRING(t.name,n,1)) l ) d ) stri ORDER BY n FOR XML PATH('') , TYPE).value('.', 'varchar(max)')) xORDER BY t.name Online Trainer For SQL DBA and Developer @RedBushTechnologies with 16 yrs exp. ChrisM@Work SSC Guru Group: General Forum Members Points: 96498 Visits: 20676 S_Kumar_S (10/5/2012)This is quite impressive but it doesn't work for some scenarios. e.g. this one :insert into t1select 'AAA22222222222222BB5BB 33CCCCCCCCC342234234'EDIT: I meant it for Chris response:SELECT t.name, x.New_Name FROM #t1 tCROSS APPLY ( SELECT New_Name = (SELECT Newletter + '' FROM ( SELECT n, Newletter = CASE WHEN d.grouper IS NOT NULL AND COUNT(*) OVER (PARTITION BY d.grouper) > 5 THEN 'x' ELSE letter END FROM ( SELECT n, letter, grouper = ROW_NUMBER() OVER(PARTITION BY t.name ORDER BY ISNUMERIC(letter), n) - N FROM (SELECT TOP(LEN(t.name)) n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM sys.columns a, sys.columns b) tally CROSS APPLY (SELECT letter = SUBSTRING(t.name,n,1)) l ) d ) stri ORDER BY n FOR XML PATH('') , TYPE).value('.', 'varchar(max)')) xORDER BY t.nameMy apologies, it was a change during coding:`SELECT t.name, x.New_Name FROM #t1 tCROSS APPLY ( SELECT New_Name = (SELECT Newletter + '' FROM ( SELECT n, Newletter = CASE WHEN ISNUMERIC(letter) = 1 -- d.grouper IS NOT NULL AND COUNT(*) OVER (PARTITION BY d.grouper) > 5 THEN 'x' ELSE letter END FROM ( SELECT t.name, n, letter, grouper = n - ROW_NUMBER() OVER(ORDER BY ISNUMERIC(letter) desc, n) FROM (SELECT TOP(LEN(t.name)) n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM sys.columns a, sys.columns b) tally CROSS APPLY (SELECT letter = SUBSTRING(t.name,n,1)) l ) d ) stri ORDER BY n FOR XML PATH('') , TYPE).value('.', 'varchar(max)')) xORDER BY t.name`Edit: found an error in an extended sample data set. “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 ModenExploring Recursive CTEs by Example Dwain Camps