how can i do this - 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 2000 Forums

SQL Server Development (2000)

how can i do this

Author

Topic

Radhiga
Starting Member

35 Posts

Posted - 2006-04-04 : 01:00:30

Hello Friends,
i would like to retrieve phone no in the following format.Countrycode-Areacode-phno . All these three are different fields in database.
so i selected like this...cc +'-'+ac+'-'+ph
now my question is i dont want to Concate '_' if countrycode and areacode are null. how can i do it. along with this i need to select some other fields also..
pls help me
thanks

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-04-04 : 01:04:32

SELECT CASE WHEN CountryCode IS NULL THEN '' ELSE CountryCode + '-' END +
CASE WHEN AreaCode IS NULL THEN '' ELSE AreaCode + '-' END +
phNo
..... etc etc
you get the idea. Will need some tweaking to get exactly what you want, but it's a starting point.

HTH,

Tim

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-04-04 : 01:11:17

Try this..
select coalesce(Countrycode+'-','')+coalesce(Areacode+'-','') + phno , column2,column3
from mytable

Radhiga
Starting Member

35 Posts

Posted - 2006-04-04 : 01:19:57

Thanks for the Reply Guys...
I tried that first one earlier..but still im not sure where did i do mistake and y didnt i get that time...now it is working fine..Thanks for the solution

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-04 : 02:15:19

Also you can use IsNull in place of coalesce

Madhivanan

Failing to plan is Planning to fail

a_r_satish
Yak Posting Veteran

84 Posts

Posted - 2006-04-04 : 06:16:23

Check this Code, if you are using the 1st Method:

DecLare
@CountryCode varchar(4),
@AreaCode varchar(4),
@Phno varchar(7)
Set @CountryCode = '123'
Set @AreaCode = '456'
Set @Phno = '789'
SELECT
CASE
WHEN @CountryCode is NULL THEN ''
ELSE @CountryCode + '-'
END +
CASE
WHEN @AreaCode is NULL THEN ''
ELSE @AreaCode + '-'
END +
@Phno

Regards,
satish.r
"Known is a drop, Unknown is an Ocean"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources