Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 A quick query puzzle: Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, October 4, 2012 6:57 AM
 SSC-Addicted Group: General Forum Members Last Login: Monday, November 28, 2016 8:56 AM Points: 414, Visits: 1,062
 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 -----Table Proc Index Performance TSQL &&%\$#@*(#@\$%.......------------Deep Into SQL Jungle
Post #1368367
 Posted Thursday, October 4, 2012 1:19 PM
 Ten Centuries Group: General Forum Members Last Login: Monday, November 28, 2016 11:55 AM Points: 1,227, Visits: 3,308
 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`
Post #1368664
 Posted Friday, October 5, 2012 3:58 AM
 SSC-Addicted Group: General Forum Members Last Login: Monday, November 28, 2016 8:56 AM Points: 414, Visits: 1,062
 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.... -----Table Proc Index Performance TSQL &&%\$#@*(#@\$%.......------------Deep Into SQL Jungle
Post #1368920
 Posted Friday, October 5, 2012 4:24 AM
 SSCrazy Eights Group: General Forum Members Last Login: Thursday, December 1, 2016 10:11 AM Points: 8,587, Visits: 18,753
 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
Post #1368929
 Posted Friday, October 5, 2012 4:52 AM
 SSC-Addicted Group: General Forum Members Last Login: Monday, November 28, 2016 8:56 AM Points: 414, Visits: 1,062
 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. -----Table Proc Index Performance TSQL &&%\$#@*(#@\$%.......------------Deep Into SQL Jungle
Post #1368934
 Posted Friday, October 5, 2012 6:36 AM
 SSC-Addicted Group: General Forum Members Last Login: Today @ 2:41 AM Points: 444, Visits: 2,158
 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
Post #1368985
 Posted Friday, October 5, 2012 6:38 AM
 SSCrazy Eights Group: General Forum Members Last Login: Thursday, December 1, 2016 10:11 AM Points: 8,587, Visits: 18,753
 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
Post #1368987
 Posted Friday, October 5, 2012 6:49 AM
 SSC-Addicted Group: General Forum Members Last Login: Today @ 2:41 AM Points: 444, Visits: 2,158
 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
Post #1368997
 Posted Friday, October 5, 2012 8:00 AM
 SSC-Addicted Group: General Forum Members Last Login: Monday, November 28, 2016 8:56 AM Points: 414, Visits: 1,062
 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 -----Table Proc Index Performance TSQL &&%\$#@*(#@\$%.......------------Deep Into SQL Jungle
Post #1369058
 Posted Friday, October 5, 2012 8:27 AM
 SSCrazy Eights Group: General Forum Members Last Login: Thursday, December 1, 2016 10:11 AM Points: 8,587, Visits: 18,753
 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
Post #1369091

 Permissions