Select substring - 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)

Select substring

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2010-02-08 : 16:39:32

Hi,

I have a column with UK postcodes like this

BB9 7DL
B1 4FH
M28 3PL

I need to select only the first part, before the space, so:

BB9
B1
M28

Thanks

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2010-02-08 : 16:57:24

various solutions, here is one:

left(postcode,charindex(' ',Postcode)-1)

Kristen
Test

22859 Posts

Posted - 2010-02-09 : 08:51:49

I would suggest

left(postcode, charindex(' ', Postcode+' ')-1)

in case [postcode] doesn't contain a space

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-09 : 08:58:51


PARSENAME(REPLACE(Postcode,' ','.'),2)

if you've only values with single part then

COALESCE(PARSENAME(REPLACE(Postcode,' ','.'),2),PARSENAME(REPLACE(Postcode,' ','.'),1))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources