strip characters out of string

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

strip characters out of string

Author

Topic

NorthernLights
Starting Member

4 Posts

Posted - 2002-01-11 : 15:32:52

I have a phone number string (416) 555-5555 in a table. I'd like to perform a search on the string so that the user is able to pass any number, and the query returns all phone numbers like it. What I'd like to do is to strip out the brackets and dashes and perform a like search.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-01-11 : 15:52:02

The REPLACE() function will do it:

SELECT * FROM PhoneNumbers
WHERE REPLACE(REPLACE(REPLACE(REPLACE(PhoneNumber, ' ',''), '(', ''), ')', ''), '-', '')
Like @searchphonenumber

mfemenel
Professor Frink

1421 Posts

Posted - 2002-01-11 : 15:52:53

If your string is always in the same format, you could do this

declare @mystring varchar(100)
set @mystring='(416) 555-5555'
set @mystring=substring(@mystring,2,3) + substring(@mystring,7,3) + right(@mystring,4)

select @mystring

or you might look at the replace function

Mike
"A program is a device used to convert data into error messages."

mfemenel
Professor Frink

1421 Posts

Posted - 2002-01-11 : 15:53:16

I've been Volked!

Mike
"A program is a device used to convert data into error messages."

Nazim
A custom title

1408 Posts

Posted - 2002-01-12 : 01:26:52

Isnt this post more suitable for Developers Forum???.

----------------------------
Anything that Doesn't Kills you Makes you Stronger

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources