I have something as under
declare @t table(city varchar(100))
insert into @t values('ab,c:d\/tyu'),('frd/hj&er\gg:')
select REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(city,',',' '),'"',''),':',''),'-',''),'/',''),'\',''),'&','')
from @t
output
----------
ab cdtyu
frdhjergg
As can be figure out that, because of too many junk characters I am using many replace function.
Is there a better and elegant way to achieve this?
One of the rare situations where a quirky update is a good idea. Don't expect the best performance, but any other approach with loops, recursive CTEs etc will have their RBAR too...
The advantage of this approach is: You can maintain your replace values in a table. You can even replace longer strings with other longer strings and - which can be really magic: By defining the order, you can even do a cascading replace...
CREATE TABLE ReplaceValues (FindChar VARCHAR(1) NOT NULL
,ReplWith VARCHAR(1) NOT NULL
,SortOrder INT NOT NULL);
INSERT INTO ReplaceValues VALUES(',',' ',1)
,('"','',2)
,(':','',3)
,('-','',4)
,('/','',5)
,('\','',6)
,('&','',7);
GO
--Normally you cannot use the quirky update inlined, but you can wrap it within a scalar function:
CREATE FUNCTION dbo.MultiReplace(@ReplaceTarget VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
--Quirky Update: One of the rare situations where this is a good idea
SELECT @ReplaceTarget=REPLACE(@ReplaceTarget,rv.FindChar,rv.ReplWith)
FROM ReplaceValues AS rv
ORDER BY rv.SortOrder;
RETURN @ReplaceTarget;
END
GO
--Your table
declare @t table(city varchar(100))
insert into @t values('ab,c:d\/tyu'),('frd/hj&er\gg:');
--...and the magic is going in here:
SELECT t.city
,dbo.MultiReplace(t.city) AS CleanedCity
FROM @t AS t
GO
--Clean-Up
DROP FUNCTION dbo.MultiReplace;
DROP TABLE ReplaceValues;
The result
city CleanedCity
ab,c:d\/tyu ab cdtyu
frd/hj&er\gg: frdhjergg
Using Function
Try this:
Create Function [dbo].[Removejunkcharacter]
(@CityTempValue varchar(100))
Returns varchar(100)
AS
Begin
Declare @value as varchar(100)
Set @value = '%[^a-z]%'
While PatIndex(@value, @CityTempValue) > 0
Set @CityTempValue = Stuff(@CityTempValue, PatIndex(@value, @CityTempValue), 1, '')
Return @CityTempValue
End
Execute Query:
Declare @t table(city varchar(100))
Insert into @t values('ab,c:d\/tyu'),('frd/hj&er\gg:')
Select t1.city AS Input,
[dbo].[Removejunkcharacter](t1.city) AS Result
FROM @t AS t1;
Result:
Input Result
ab,c:d\/tyu abcdtyu
frd/hj&er\gg: frdhjergg
Try This:
declare @t table(id int, value varchar(15))
insert @t values(1, 'ab,c:d\/tyu')
insert @t values(2, 'frd/hj&er\gg')
;with t1 as
(
select value i, id from @t
union all
select cast(replace(i, substring(i, PatIndex('%[^a-z0-9]%', i), 1), '')
as varchar(15)), id
from t1
where PatIndex('%[^a-z0-9]%', i) > 0
)
select t2.value as [Input], t1.i as [Result] from t1
join @t t2
on t1.id = t2.id
where PatIndex('%[^a-z0-9]%', i) = 0
option (maxrecursion 0)
I place this as a second answer as it is a completely new, set-based approach
As in my other answer I use a maintainable replace value's table:
CREATE TABLE ReplaceValues (FindChar VARCHAR(1) NOT NULL
,ReplWith VARCHAR(1) NOT NULL
,SortOrder INT NOT NULL);
INSERT INTO ReplaceValues VALUES(',',' ',1)
,('"','',2)
,(':','',3)
,('-','',4)
,('/','',5)
,('\','',6)
,('&','',7);
GO
declare @t table(city varchar(100))
insert into @t values('ab,c:d\/tyu'),('frd/hj&er\gg:');
--The query will use a Tally table to generate a running number on the fly. Then SUBSTRING is used to pick each and any single character and find it in the replace value's table. If nothing is found, the value is used, otherwise the replace value. Finally the separated characters are re-concatenated using FOR XML
WITH Tally AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Nmbr FROM master..spt_values) --approx 2500 values. Need more? Use CROSS JOIN
SELECT t.city
,(
SELECT ISNULL(rv.ReplWith,SUBSTRING(t2.city,Nmbr,1))
FROM @t AS t2
CROSS APPLY(SELECT TOP (LEN(t2.City)) Nmbr FROM Tally) AS A
LEFT JOIN ReplaceValues AS rv ON rv.FindChar=SUBSTRING(t2.city,Nmbr,1)
WHERE t.city=t2.city
ORDER BY A.Nmbr
FOR XML PATH(''),TYPE
).value(N'.','nvarchar(max)') ReBuilt
FROM @t AS t
GO
--Clean-Up
DROP TABLE ReplaceValues;
Here is another fairly simple solution based on Shnugo's ReplaceValues table and a recursive cte:
Shnugo's table:
CREATE TABLE ReplaceValues (FindChar VARCHAR(1) NOT NULL
,ReplWith VARCHAR(1) NOT NULL
,SortOrder INT NOT NULL);
INSERT INTO ReplaceValues VALUES(',',' ',1)
,('"','',2)
,(':','',3)
,('-','',4)
,('/','',5)
,('\','',6)
,('&','',7);
GO
Sample data:
declare @t table(city varchar(100))
insert into @t values('ab,c:d\/tyu'),('frd/hj&er\gg:')
Query:
;WITH CTE AS
(
SELECT city, 1 as level
FROM @T
UNION ALL
SELECT CAST(Replace(city,FindChar,ReplWith) as varchar(100)), level + 1
FROM CTE
INNER JOIN ReplaceValues ON level = SortOrder
)
select TOP 1 WITH TIES City
from CTE
ORDER BY level DESC
Result:
City
1 frdhjergg
2 ab cdtyu
