removing special characters

Question

Post reply

removing special characters

sql_learner29

SSCrazy Eights

Points: 9107
More actions
July 28, 2010 at 2:25 pm

#225524

Hi all,
I have a source table from which I am reading data and a column named as address has many different special characters in it that we want to remove....
but any special character may come from source,anytime.
Is there any way to remove special character when reading data from source.
here is sample data:
CREATE TABLE [dbo].[abc](
[adrs] [nvarchar](43) NULL
)
INSERT INTO abc ( adrs) VALUES ( ' 4 one ST 1FL>')
INSERT INTO abc ( adrs) VALUES ( ' 4 two ST 2FL< ')
INSERT INTO abc ( adrs) VALUES ( ' 60? three av 1FL>')
INSERT INTO abc ( adrs) VALUES ( ' 20 ? first st PD < ')
INSERT INTO abc ( adrs) VALUES ( ' 3 clear st < ')
any help?
Thanks
[font="Comic Sans MS"]
---------------------------------------------------
Thanks [/font]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

How do you know which character needs to be considered as "special"?

E.g. space, ?, < or > might be considered valid or not...

Do you have a list of valid character (or even better, a ASCII range)?

Your expected output based on your sample would really help... 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 2

The output of my set should be without ?,? ,<,>...

Well I dont have any list of characters, but yes I can put it like this I want to replace anything other than [0-9] , [A- Z], or @, - sign in my address.

Can this be done, I dont want to remove spaces...

Thanks

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

I'm not massively happy with this, but it works for how I understand your requirements.

--Sample Data

DECLARE @TABLE AS TABLE(

[adrs] NVARCHAR(43))

INSERT INTO @TABLE

SELECT ' ?4 one ST 1FL>'

UNION ALL SELECT ' ?4 two ST 2FL< '

UNION ALL SELECT ' ?60? three av 1FL>'

UNION ALL SELECT ' ?20 ? first st PD < '

UNION ALL SELECT ' ?3 clear st < '

--Query

;WITH num1 (n)

AS (SELECT 1

UNION ALL

SELECT 1),

num2 (n)

AS (SELECT 1

FROM num1 AS adrs,

num1 AS adrs2),

num3 (n)

AS (SELECT 1

FROM num2 AS adrs,

num2 AS adrs2),

num4 (n)

AS (SELECT 1

FROM num3 AS adrs,

num3 AS adrs2),

nums (n)

AS (SELECT Row_number() OVER(ORDER BY n)

FROM num4),

cleaner

AS (SELECT [adrs],

(SELECT CASE

WHEN Substring([adrs], n, 1) LIKE '%[a-zA-Z0-9]%' THEN

Substring([adrs], n, 1)

WHEN Substring([adrs], n, 1) LIKE '@' THEN

Substring([adrs], n, 1)

WHEN Substring([adrs], n, 1) LIKE '-' THEN

Substring([adrs], n, 1)

ELSE ' '

END

FROM nums

WHERE Len([adrs]) >= n

FOR XML PATH('')) AS new_adrs

FROM @table)

SELECT adrs, REPLACE(new_adrs, ' ', ' ')

FROM cleaner;

adrs

------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

?4 one ST 1FL> 4 one ST 1FL

?4 two ST 2FL< 4 two ST 2FL

?60? three av 1FL> 60 three av 1FL

?20 ? first st PD < 20 first st PD

?3 clear st < 3 clear st

-EDIT-

Strange, the forums are changing my SQL. The SELECT at the bottom of the CTE should read: -

SELECT adrs, REPLACE(new_adrs, '& # x 2 0;', ' ')

FROM cleaner;

But with no spaces between '& # x 2 0;'

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/