sql server query replace

Question

0.00/5 (No votes)

See more:

how to replace multiple different characters('+','-','/','(',')') in single replace function.
i know using replace(replace(replace(replace(ssdsdsd,se,we) bu,t want in single replace.
plzz anyone can help me??

Posted 15-Feb-12 18:12pm

rajdeep kumar

Add a Solution

Comments

manognya kota 16-Feb-12 0:16am

Can you exactly tell what all you want to replace with what?

thatraja 16-Feb-12 0:33am

Show full code/query

rajdeep kumar 16-Feb-12 0:34am

Hi,
i want to replace this number +(60) 2345(0)-324 into 6023450324
with only single replace function.

rajdeep kumar 16-Feb-12 0:40am

this is my number= +(60) 2345(0)-324
i used this query to replace all characters with ''
LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(OffPhoneNo,' ',''),'.',''),'-',''),'+',''),'(',''),')',''),CHAR(9),'')))

so is there other way to replace this numer with single replace function???

2 solutions

Solution 2

Dear Friend,

I think you have to use the functionality of SPLIT function in SQL Server in order to achieve you require.

http://geekswithblogs.net/AngelEyes/archive/2007/04/12/111504.aspx[^]

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648[^]

If this helps you out then mark this as your answer.

Thanks

Posted 15-Feb-12 18:47pm

Varun Sareen

v2

Comments

rajdeep kumar 16-Feb-12 0:52am

no , i don't want to use any function. just tell me in singe select statement? pllllzzzzz

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

thatraja · Accepted Answer · 2012-02-15T18:44:00

Solution 1

Better way is create an User defined function[^] with those many Replaces & ans use it in your SQL SELECT statement

EDIT

SQL

CREATE FUNCTION Fn_Phone
(
    @OffPhoneNo VARCHAR(100) 
)
    RETURNS VARCHAR(100)
AS
BEGIN
  RETURN(LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@OffPhoneNo,' ',''),'.',''),'-',''),'+',''),'(',''),')',''),CHAR(9),''))))
END

Now use it in SELECT statement

SQL

SELECT OffPhoneNo, dbo.FnPhone(OffPhoneNo) FROM [TableName]

Posted 15-Feb-12 18:44pm

thatraja

Updated 15-Feb-12 22:52pm

v3

Comments

rajdeep kumar 16-Feb-12 0:46am

can u please tell me code, how to do this function?

thatraja 16-Feb-12 0:59am

Check my updated answer

thatraja 16-Feb-12 2:19am

What happened? you have accepted my 2 times & rejected 2 times
:confused:

[no name] 16-Feb-12 1:39am

+5And what if we need to replace the numbers of rows of the table?

thatraja 16-Feb-12 2:02am

It'll work. Just try that.

BTW you forgot to vote.

thatraja 16-Feb-12 4:56am

Check my answer again, I have updated it. And It's a working query & function

rajdeep kumar 16-Feb-12 4:26am

dear friends,
thanks for ur help, but it work only for single variable,
but my requirement is not for single variable, it should be for numbers of rows in any table which has one column(offphone) and contains so many records for offphone.
so i want instead of column name in select statement instead of any fixed variable in select statement.
so i can i pass column name in Fn_Phone, which you had created.

plz help me.

thatraja 16-Feb-12 4:56am

Check my answer again, I have updated it. And It's a working query & function

rajdeep kumar 16-Feb-12 5:10am

Thanks for ur smart answer.

Varun Sareen 16-Feb-12 11:59am

if the no of special characters increase then what, you will increase the the number of replace function?? Still its working so its good :)

thatraja 16-Feb-12 12:11pm

I just gave him an idea(Creating UDF....BTW it's a sample function & OP can modify the UDF based on his requirement)

Varun Sareen 16-Feb-12 12:49pm

OK :)