SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A quick query puzzle:


A quick query puzzle:

Author
Message
S_Kumar_S
S_Kumar_S
Say Hey Kid
Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)

Group: General Forum Members
Points: 674 Visits: 1062
Hi
I 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))
GO
INsert into t1
select '1234ABC123456XYZ1234567890ADS'
GO
INsert into t1
select 'cbv736456XYZ543534534545XLS'
GO



EXPECTED RESULT:

1234ABCxxxxxxXYZxxxxxxxxxxADS
cbvxxxxxxXYZxxxxxxxxxxxxXLS

drop table t1

-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
mickyT
mickyT
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1576 Visits: 3317
Hi

You 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
S_Kumar_S
Say Hey Kid
Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)

Group: General Forum Members
Points: 674 Visits: 1062
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
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16227 Visits: 19546
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 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
Exploring Recursive CTEs by Example Dwain Camps
S_Kumar_S
S_Kumar_S
Say Hey Kid
Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)

Group: General Forum Members
Points: 674 Visits: 1062
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
ben.brugman
ben.brugman
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1048 Visits: 2391
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 with

1
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16227 Visits: 19546
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 t
CROSS 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)')
) x
ORDER 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 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
Exploring Recursive CTEs by Example Dwain Camps
ben.brugman
ben.brugman
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1048 Visits: 2391
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 with

1 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 xxxxxxxx12345678

x1 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
S_Kumar_S
Say Hey Kid
Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)

Group: General Forum Members
Points: 674 Visits: 1062
This is quite impressive but it doesn't work for some scenarios. e.g. this one :
insert into t1
select 'AAA22222222222222BB5BB 33CCCCCCCCC342234234'

EDIT: I meant it for Chris response:
SELECT
t.name, x.New_Name
FROM #t1 t
CROSS 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)')
) x
ORDER BY t.name

-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16227 Visits: 19546
S_Kumar_S (10/5/2012)

This is quite impressive but it doesn't work for some scenarios. e.g. this one :
insert into t1
select 'AAA22222222222222BB5BB 33CCCCCCCCC342234234'

EDIT: I meant it for Chris response:
SELECT
t.name, x.New_Name
FROM #t1 t
CROSS 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)')
) x
ORDER BY t.name


My apologies, it was a change during coding:

SELECT 
t.name, x.New_Name
FROM #t1 t
CROSS 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)')
) x
ORDER 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 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
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search