January 3, 2013 at 4:01 pm
I'm wondering how might I search every character in a string for numeric and alpha characters?
Numeric characters I want to replace with a # sign. Alpha characters I want to replace with an A.
I know how to use REPLACE Function. But that only search a record for each appearance of ONE set of characters. I want every number or alphabet changed.
I could do something very long and complicated like
SELECT REPLACE(REPLACE(REPLACE(ColumnName, 1, '#'), 2,'#'), 3,'#')
FROM TableName
But this doesn't work so well with Alphabets as there are 26 characters and its not very maintenance friendly.
Thanks!
January 3, 2013 at 4:05 pm
Embedded REPLACEs is your best option really, from a performance standpoint.
You could generate the REPLACE statements so you didn't have to write them by hand.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 3, 2013 at 4:28 pm
You mean something like this?
DECLARE @TestStr VARCHAR(20) = 'Q2ERY56920TYEU';
SELECT @TestStr;
WITH eTally(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns a CROSS JOIN sys.all_columns b)
SELECT
NewString = STUFF((SELECT '' + NewChar FROM
(SELECT TOP (LEN(@TestStr))
n,
CASE WHEN SUBSTRING(@TestStr,n,1) NOT LIKE '[^0-9]' THEN '#' ELSE 'A' END NewChar
FROM
eTally)dt
ORDER BY n
FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,0,'');
January 3, 2013 at 4:33 pm
Lynn Pettis (1/3/2013)
You mean something like this?
DECLARE @TestStr VARCHAR(20) = 'QWERY56920TYEU';
SELECT @TestStr;
WITH eTally(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns a CROSS JOIN sys.all_columns b)
SELECT
NewString = STUFF((SELECT '' + NewChar FROM
(SELECT TOP (LEN(@TestStr))
n,
CASE WHEN SUBSTRING(@TestStr,n,1) NOT LIKE '[^0-9]' THEN '#' ELSE 'A' END NewChar
FROM
eTally)dt
FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,0,'');
Couldn't the first test just be "LIKE [0-9]"?
And I think you need a second WHEN to check for alphas, with an ELSE that uses the original character as is.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 3, 2013 at 4:35 pm
Depends on the OPs actually requirements.
January 3, 2013 at 4:39 pm
declare @t table(id int, string varchar(500))
insert @t values (1,'abc0123456789'),(2,'123abcdefghijklmnopqrstuvwzyz');
with cte as
(
select id,1 nbr, cast(string as varchar(500)) string
from @t
union all
select id,nbr+1, cast(
case when patindex('%[0-9]%',string) > 0
then replace(string,substring(string,patindex('%[0-9]%',string),1),'#')
when patindex('%[a-zA-z]%',string) > 0
then replace(string,substring(string,patindex('%[a-zA-z]%',string),1),'@')
end
as varchar(500))
from cte
where patindex('%[0-9a-zA-Z]%',string) > 0
)
select id, replace(string,'@','A') string from
(select *,row_number()over(partition by id order by nbr desc)rnk from cte)d
where rnk=1
January 3, 2013 at 4:41 pm
Your pick:
DECLARE @TestStr VARCHAR(20) = ' Q2ERY56,920 TYEU';
SELECT @TestStr;
WITH eTally(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns a CROSS JOIN sys.all_columns b)
SELECT
NewString = STUFF((SELECT '' + NewChar FROM
(SELECT TOP (LEN(@TestStr))
n,
CASE WHEN SUBSTRING(@TestStr,n,1) NOT LIKE '[^0-9]' THEN '#' ELSE 'A' END NewChar
FROM
eTally)dt
ORDER BY n
FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,0,'');
GO
DECLARE @TestStr VARCHAR(20) = ' Q2ERY56,920 TYEU';
SELECT @TestStr;
WITH eTally(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns a CROSS JOIN sys.all_columns b)
SELECT
NewString = STUFF((SELECT '' + NewChar FROM
(SELECT TOP (LEN(@TestStr))
n,
CASE WHEN SUBSTRING(@TestStr,n,1) LIKE '[0-9]' THEN '#'
WHEN SUBSTRING(@TestStr,n,1) LIKE '[A-Za-z]' THEN 'A'
ELSE SUBSTRING(@TestStr,n,1) END NewChar
FROM
eTally)dt
ORDER BY n
FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,0,'');
January 3, 2013 at 5:16 pm
declare @t table(id int, string varchar(500))
insert @t values (1,'abcABC,0123456789~!@'),(2,'123abcdefghijklmnopqrstuvwzyz');
with cte1 as
(
select id,
0 nbr,
cast(string as varchar(500)) string
from @t
union all
select id,
nbr + 1,
cast(replace(string,nbr,'#') as varchar(500))
from cte1
where nbr < 10
),
cte2 as
(
select id,
65 nbr,
cast(string as varchar(500)) string
from (select *, row_number()over(partition by id
order by nbr desc)rnk
from cte1
)d
where rnk = 1
union all
select id,
nbr +1,
cast(replace(string,char(nbr),'_') as varchar(500))
from cte2
where nbr < 65+26+1
)
select id, replace(string,'_','A') string
from (select *, row_number() over(partition by id order by nbr desc) rnk from cte2)d
where rnk = 1
January 3, 2013 at 5:20 pm
zombieisdead2020 (1/3/2013)
declare @t table(id int, string varchar(500))insert @t values (1,'abcABC,0123456789~!@'),(2,'123abcdefghijklmnopqrstuvwzyz');
with cte1 as
(
select id,
0 nbr,
cast(string as varchar(500)) string
from @t
union all
select id,
nbr + 1,
cast(replace(string,nbr,'#') as varchar(500))
from cte1
where nbr < 10
),
cte2 as
(
select id,
65 nbr,
cast(string as varchar(500)) string
from (select *, row_number()over(partition by id
order by nbr desc)rnk
from cte1
)d
where rnk = 1
union all
select id,
nbr +1,
cast(replace(string,char(nbr),'_') as varchar(500))
from cte2
where nbr < 65+26+1
)
select id, replace(string,'_','A') string
from (select *, row_number() over(partition by id order by nbr desc) rnk from cte2)d
where rnk = 1
The recursive CTE may not scale well as the length of the strings increase. Both sets of options really need to be tested to determine which is better solution.
January 3, 2013 at 5:27 pm
The recursive CTE may not scale well as the length of the strings increase. Both sets of options really need to be tested to determine which is better solution.
My recursive CTE solutions is not depended to length of any string.
January 3, 2013 at 5:49 pm
I just love having something in my toolbox to deal with stuff like this. Refer to the 4th link in my signature to find PatternSplitCM and then try this:
;WITH
TestStrings (ItemNo, TestStr) AS (
SELECT 1, 'Q2ERY56920TYEU_$%' UNION ALL SELECT 2, 'I LOVE SSC 100% IN 2013'),
SplitStrings AS (
SELECT ItemNo, Item1=a.ItemNumber, Item2=b.ItemNumber
,Item=CASE WHEN a.Matched = 1 THEN REPLICATE('#', LEN(a.Item))
WHEN b.Matched = 1 THEN REPLICATE('A', LEN(b.Item))
ELSE b.Item END
FROM TestStrings
CROSS APPLY PatternSplitCM(TestStr, '[0-9]') a
CROSS APPLY PatternSplitCM(a.Item, '[A-Za-z]') b)
SELECT (
SELECT Item + ''
FROM SplitStrings b
WHERE a.ItemNo = b.ItemNo
ORDER BY Item1, Item2
FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)')
FROM SplitStrings a
GROUP BY ItemNo
The article was actually based on solving a similar problem, which you can read about therein.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 3, 2013 at 8:14 pm
zombieisdead2020 (1/3/2013)
The recursive CTE may not scale well as the length of the strings increase. Both sets of options really need to be tested to determine which is better solution.
My recursive CTE solutions is not depended to length of any string.
I didn't say it was dependent on the length of any string. I said it MAY NOT scale well as the length of the strings increased and that tests should be run to determine how well each of the solutions work. Nothing more, nothing less.
January 4, 2013 at 7:23 am
Thanks very much for all the replies. I greatly appreciate them.
January 4, 2013 at 8:53 am
Those embedded REPLACEs are looking more tempting now, huh? 🙂
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply