Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

A quick query puzzle: Expand / Collapse
Author
Message
Posted Thursday, October 4, 2012 6:57 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 3:34 AM
Points: 378, Visits: 886

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
Post #1368367
Posted Thursday, October 4, 2012 1:19 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 3:01 AM
Points: 984, Visits: 2,983
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

Post #1368664
Posted Friday, October 5, 2012 3:58 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 3:34 AM
Points: 378, Visits: 886

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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:21 AM
Points: 7,176, Visits: 13,623
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
Post #1368929
Posted Friday, October 5, 2012 4:52 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 3:34 AM
Points: 378, Visits: 886
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 Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, August 2, 2014 1:47 AM
Points: 246, Visits: 1,168
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
Post #1368985
Posted Friday, October 5, 2012 6:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:21 AM
Points: 7,176, Visits: 13,623
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
Post #1368987
Posted Friday, October 5, 2012 6:49 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, August 2, 2014 1:47 AM
Points: 246, Visits: 1,168
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
Post #1368997
Posted Friday, October 5, 2012 8:00 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 3:34 AM
Points: 378, Visits: 886

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
Post #1369058
Posted Friday, October 5, 2012 8:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:21 AM
Points: 7,176, Visits: 13,623
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
Post #1369091
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse