Splitting a string in SQL using MySQL

Question

I need to take a postal code looking like:

S7Y 6H5

that's in a table and display it as

S7Y & 6H5

I can't find a command that splits the string in sql.

Why do you have to do this in SQL? Can't you just do it once you've retrieved the information? Not saying your approach is wrong, but just wondering if this is really necessary. — aug
– aug, Commented Mar 11, 2013 at 19:51
Is it only ever a single space, and you you need the literal 'S7Y & 6H5', or do you want them in 2 seperate fields/columns? — Wrikken
– Wrikken, Commented Mar 11, 2013 at 19:51
also you should be able to use SUBSTR along with concatenation to make all kinds of variants. — Randy
– Randy, Commented Mar 11, 2013 at 19:57

Jeff Ferland · Accepted Answer · 2013-03-11 19:52:37Z

3

MySQL doesn't include a split function, but it does include a replace. You could use:

SELECT REPLACE('S7Y 6HS', ' ', ' & ')

The greater question for me, though, is why don't you do that in your application code?

answered Mar 11, 2013 at 19:52

Jeff Ferland

18.4k7 gold badges49 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

www · Accepted Answer · 2013-03-11 19:52:42Z

2

Try this:

Select REPLACE ('S7Y 6H5', ' ', ' & ')

answered Mar 11, 2013 at 19:52

www

4,4011 gold badge26 silver badges24 bronze badges

echo_Me · Accepted Answer · 2013-03-11 19:56:09Z

1

you can use INSERT INSERT(str,pos,len,newstr)

     SELECT INSERT('S7Y 6H5', 4, 0, ' & ');

answered Mar 11, 2013 at 19:56

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges