Help in query for concatinating 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

SQL Server 2005 Forums

Transact-SQL (2005)

Help in query for concatinating string

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2008-07-21 : 05:50:58

I am

SELECT T.city_name + ', ' + ISNULL(s.state_province_name,'') as CityName, T.city_id as CityId
FROM country C join city t on t.country_id = c.country_id
left join state_province s on t.state_province_id = s.state_province_id
where T.active = 1
and T.country_id = @countryID
order by T.city_name

I am gettin City with state as
Karachi,Sind
But if state is null i got city like
Islamabad,
what i need is like not to have , when state is null
Islamabad

Some body suggested COALESCE but i don't know how to use it
Kamran Shahid
Sr. Software Engineer(MCSD.Net,MCPD.net)
www.netprosys.com

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2008-07-21 : 05:53:43

Sample data is

Chicago, Illinois
Chicago,
CityA CA, California
CityA CA, New York
CityA NY, New York
citya va, Virginia
cityb ca, California
CVVV, CAProvicneA
Islamabad,
Toronto, CAProvicneA

Kamran Shahid
Sr. Software Engineer(MCSD.Net,MCPD.net)
www.netprosys.com

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-21 : 05:54:50

SELECT T.city_name + ISNULL(', '+s.state_province_name,'')

Em

Thiyagu_04
Starting Member

37 Posts

Posted - 2008-07-21 : 06:55:25

SELECT coalesce(T.city_name + ', '+s.state_province_name,city) as CityName, T.city_id as CityId
FROM country C join city t on t.country_id = c.country_id
left join state_province s on t.state_province_id = s.state_province_id
where T.active = 1
and T.country_id = @countryID
order by T.city_name

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources