Working with Strings - 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

Development Tools

Reporting Services Development

Working with Strings

Author

Topic

idea01
Starting Member

2 Posts

Posted - 2009-07-25 : 12:29:18

Hi everyone,

I've got a report that i'm working on that has a field that has both a city and state returned from the DB.

EXAMPLE "Houston, TX" or "Seattle, WA"

is there a way to remove the state from the string, not knowing what the state may be?

Thanks,
JeffP

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-25 : 12:38:41

SELECT SUBSTRING(Col1, 1, LEN(Col1) - 4)
FROM Table1
WHERE Col1 LIKE '%, [A-Z][A-Z]'

N 56°04'39.26"
E 12°55'05.63"

idea01
Starting Member

2 Posts

Posted - 2009-07-25 : 13:09:22

awesome, Thank you!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-27 : 02:32:35

If it has always State

SELECT SUBSTRING(Col1, 1, LEN(Col1) - 4)
FROM Table1

Madhivanan

Failing to plan is Planning to fail

yavvie
Starting Member

46 Posts

Posted - 2009-07-27 : 09:26:18

or you could find the position of the last comma and delete everything from that comma on in case you don't get the state returned (I dont know about your data but in mine I would expect it :))

enmadrid
Starting Member

5 Posts

Posted - 2009-07-27 : 16:36:51

You can return a substring ending at the comma. Here's a quick example:

create table ##temp_st (
city_state varchar(100) null
)

insert into ##temp_st
select 'Houston, TX'

insert into ##temp_st
select 'Seattle, WA'

select city_state, Charindex(',', city_state, 1) as comma_position,
substring(city_state, 1, Charindex(',', city_state, 1) - 1) as trimmed_string
from ##temp_st

drop table ##temp_st

Cheers,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources