In ColdFusion we have a bunch of list functions, but what about SQL?
I wanted to return only the outcode portion of a postcode. So from 'SW11 3UZ' I wanted only 'SW11'.
Now the first part is of variable length, so I couldn't use LEFT(postcode, 4), and the space might not be a space, so CHARINDEX(' ') is out too because it could be a tab or two spaces, or two tabs, or a tab and a space, or two tabs and a space, or three spaces... you can see where I'm going with this.
So what to do? Well, regular expressions are always good, so using PATINDEX we can say, "find me the first index of a space or a tab" with:
PATINDEX('%[ ]%', postcode) Then give me everything up to that index:
SUBSTRING(postcode, 1, PATINDEX('%[ ]%', postcode)) With the full SELECT statement, with a DINSTINCT, as:
SELECT DISTINCT SUBSTRING(postcode, 1, PATINDEX('%[ ]%', postcode)) outcode FROM property Note: there is a space and a tab between the [ and ].
As it is, this will return one of the white space characters along with the outcode. So you're probably thinking the same as me, add a -1 to SUBSTRING to remove it. But what about when the pattern isn't matched and PATINDEX returns 0? Well, we'll end up with SUBSTRING(1, -1, postcode) and we get an error because the second argument to SUBSTRING needs to be positive.
So we can either live with the white space or we can add RTRIM() to remove it. Which leaves us with the working example:
SELECT DISTINCT RTRIM(SUBSTRING(postcode, 1, PATINDEX('%[ ]%', postcode))) outcode FROM property