Converting a string to a generalized format

Question

Post reply

Converting a string to a generalized format

pandeesh

Say Hey Kid

Points: 673
More actions
June 24, 2011 at 8:37 am

#253385

Hi,
My input data(varchar column in a table) will look like this:
(123) 456 (789)
1234 (567) - 89
123-456-789
and while selecting from the table my output should look like this.
Whatever numeric is there in that ,that should be replaced by 9
(999) 999 (999)
9999 (999) - 99
999-999-999
How to achieve this in sql server ?
Thanks
Thanks,
Pandeeswaran

Viewing 8 posts - 1 through 7 (of 7 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

that is an odd requirement but you can achieve this with nested replace statements easily enough.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323518 More actions · Answer 2

Sean Lange (6/24/2011)
that is an odd requirement but you can achieve this with nested replace statements easily enough.

and nested replaces are really fast too;

here's an example, spread out for readability/unserstand-ability, but you could cendense it to a single line.

with mySampleData(ThePhoneNumber)

AS

(

SELECT '(123) 456 (789)' UNION ALL

SELECT '1234 (567) - 89' UNION ALL

SELECT '123-456-789'

)

SELECT

REPLACE(

REPLACE(ThePhoneNumber,'0','9'),

'1','9'),

'2','9'),

'3','9'),

'4','9'),

'5','9'),

'6','9'),

'7','9'),

'8','9')

FROM MySampleData

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

pandeesh Say Hey Kid Points: 673 More actions · Answer 3

Yes! Obviously it can be achieved via nested replace statements.

But i still wonder that why REGEXP_REPLACE is not yet introduced in Sql Server.

I am expecting this in the next release.

If it's available then we can achieve this in a single line:

e,g:

SELECT REGEXP_REPLACE(phone_no,'[0-9]','9') from table.

this feature is already available in oracle from 10gR2 .

Now i am looking this in sql server too.

Thanks

Thanks,
Pandeeswaran

Lowell SSC Guru Points: 323518 More actions · Answer 4

you can easily add a CLR for regular expressions, so since it's something you can do yourself, i don't think you'll ever see it included in a future version.

search fro CLR regular expressions, it's one of the "hello world" type CLR's out there, and then you can use that instead.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

pandeesh (6/26/2011)
Yes! Obviously it can be achieved via nested replace statements.
But i still wonder that why REGEXP_REPLACE is not yet introduced in Sql Server.
I am expecting this in the next release.
If it's available then we can achieve this in a single line:
e,g:
SELECT REGEXP_REPLACE(phone_no,'[0-9]','9') from table.
this feature is already available in oracle from 10gR2 .
Now i am looking this in sql server too.
Thanks

Regex Replace won't be faster than the nested replaces. Behind the scenes, they do the same thing.

Also, they may have changed it for one of the later versions of Oracle but Regex was not a "given" in previous versions. It has to be loaded as part of a separate module. You can achieve the same functionality by loading a CLR or running Poweshell in SQL Server.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pandeesh Say Hey Kid Points: 673 More actions · Answer 6

Regex Replace won't be faster than the nested replaces. .

May i know how?

Thanks,
Pandeeswaran

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

pandeesh (6/27/2011)
Regex Replace won't be faster than the nested replaces. .
May i know how?

Yep.... like I said...

Behind the scenes, they do the same thing.

However, if you want, I can setup a million row table. You write the Regex Replace so I can run it and I'll use the Nested Replace and we'll see which wins.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)