can i get phone number - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

can i get phone number

Author

Topic

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-07-28 : 06:13:42

hi all,

i want to select phone numbers from a column phone as follows.
only two cases to consider and also all spaces should be avoided.

case1
input +44 (0) 113 386 6400
output 1133866400

case2
input 1133866400
output 1133866400

thanks in advance.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-28 : 06:24:07

[code]
select replace
(
case when charindex(')',data)>0 then substring(data,charindex(')',data)+1,len(data))
else data end,' ',''
) as data
from
(
select '+44 (0) 113 386 6400' as data union all
select '1133866400'
) as t[/code]

Madhivanan

Failing to plan is Planning to fail

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-07-28 : 06:34:19

HI Madhivanan,
thanks
it works great...

ok.

VGuyz
Posting Yak Master

121 Posts

Posted - 2008-07-28 : 06:41:41

Check this,
No need to go for case statement.

select right
((replace('+44 (0) 113 386 6400',' ','')),
(len(replace('+44 (0) 113 386 6400',' ',''))-(charindex( ')',replace('+44 (0) 113 386 6400',' ','')))
))

VGuyz
Posting Yak Master

121 Posts

Posted - 2008-07-28 : 06:50:53

Is there any wrong in my query.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-07-28 : 08:53:00

Hi VGuyz

Your query provides the right answers I don't know if it is quicker or slower than Madhivanan's approach. I think Madhivanan's method is easier to debug though.

Consider '+44 (0) 113 386 6400'

Your query performs on this string...
3 x REPLACE
1 X LEN
1 X CHARINDEX

The CASE statement performs on this string...
1 x CASE overheads
1 x REPLACE
1 x LEN
1 x SUBSTRING
2 x CHARINDEX

so if it takes less time to do 2 additional REPLACES than it takes to process the CASE statement with 1 additional SUBSTRING and CHARINDEX then your query will be quicker. Both approaches perform 5 string ops.

However that's the best scenario for your statement. Consider what happens for the 2nd example string.....

Consider '1133866400'

Your query performs on this string...
3 x REPLACE
1 X LEN
1 X CHARINDEX

The CASE statement performs on this string...
1 x CASE overheads
1 x CHARINDEX check
1 x REPLACE

So you are still doing 5 string operations to the CASE statement's 2

I have a feeling that your approach might be faster on a dataset comprising mostly strings with () in them, and the Case approach faster on datasets with mostly wholly numerical strings.

-------------
Charlie

VGuyz
Posting Yak Master

121 Posts

Posted - 2008-07-28 : 09:13:12

Hi charlie,
its really nice.
Thanks for u'r information.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources