CODEWITHSUNDEEP
sqlsql-server-2008
EileenS
EileenS

Reputation: 43

SQl string split (parse) on space where some field values have no space

I've been trying to split a string value in my query to return the first part of a two part postcode in a new column. Some values have only the first part, and some have both. After a bit of searching I found this code:

SELECT
  TblPLSSU.PLSSUPostcode  
  ,SUBSTRING(TblPLSSU.PLSSUPostcode, 1, CHARINDEX(' ', TblPLSSU.PLSSUPostcode)) AS PCode
FROM 
TblPostcodes

This happily splits the values that have two parts to the postcode but it seems to be ignoring the single part post codes.

For example, values for TblPLSSU.PLSSUPostcode might be:

EH1 1AB 
EH2

I want to return the values

EH1 
EH2

But with the code above I am only getting EH1

Thanks Eils

Upvotes: 1

Views: 740

Answers (3)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

A little trick with STUFF function. This works because of CHARINDEX returns index of the first occurrence. So you are just adding space to strings and replacing all symbols from first occurrence till the end with empty string:

DECLARE @t TABLE(v VARCHAR(20))
INSERT INTO @t VALUES('EH1 1AB'), ('EH2')

SELECT STUFF(v + ' ', CHARINDEX(' ', v + ' '), LEN(v), '')
FROM @t

Another version:

SELECT SUBSTRING(v + ' ', 1, CHARINDEX(' ', v + ' ') - 1)
FROM @t

Output:

EH1
EH2

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Use case to pick up post codes as-is when they don't have a space separated value. 

SELECT
PLSSUPostcode  
,case when CHARINDEX(' ', PLSSUPostcode) > 0 
 then SUBSTRING(PLSSUPostcode, 1, CHARINDEX(' ', PLSSUPostcode))
else PLSSUPostcode end AS PCode
FROM 
TblPostcodes

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269603

Use case as well:

SELECT TblPLSSU.PLSSUPostcode, 
       (CASE WHEN TblPLSSU.PLSSUPostcode LIKE '% %'
             THEN SUBSTRING(TblPLSSU.PLSSUPostcode, 1, CHARINDEX(' ', TblPLSSU.PLSSUPostcode)) 
        END) AS PCode
FROM . . .

Upvotes: 1

Related Questions