CODEWITHSUNDEEP
mysql
Boomfelled
Boomfelled

Reputation: 535

Selecting a substring if it's not an integer

I'm selecting two substrings from the same column, in this instance it's a UK postcode.

What I'd like to do is only select the second if it is NOT an integer.

Ultimately I'm going to be writing both substrings (the second if it's not an integer) to a new column.

SELECT SUBSTRING(postcode,1,1) AS one, SUBSTRING(postcode,2,1) AS two

Can I wrap an IF Statement around the second substring?

SELECT SUBSTRING(postcode,1,1) AS one, IF NOT INT(SUBSTRING(postcode,2,1)) AS two

Many thanks

Upvotes: 0

Views: 74

Answers (1)

Kickstart
Kickstart

Reputation: 21513

Possible (assuming you are happy with 2 columns coming back).

SELECT SUBSTRING(postcode,1,1) AS one, IF(SUBSTRING(postcode,2,1) REGEXP '^-?[0-9]+$', NULL, SUBSTRING(postcode,2,1)) AS two 
FROM Postcodes

Might get a bit more messy if you want to cope with more complex postcodes such as EC1A 1BB , depending on whether you want the A returned or not and under what circumstances.

Upvotes: 1

Related Questions