[SOLVED] Field Split on Database Level

[wsantos]

We are trying to do a match up of database from one server to another. However the thing is we want to split a certain columns into three columns since it contains three data in that single field. Can this be done on the database level?

[kirk112]

is all the data in the columns you want to split the same lenght?

[wsantos]

nope...data is

 

"XXXXXXXXXXXXX G9999-99999"

 

we want to split it into

 

1: XXXXXXXXXXXXX

2: G9999

3: 99999

[kirk112]

is XXXXXXXXXXXXX always 13 chars long and is field 2 and 3 always split by a -

[wsantos]

Nope XXXXXX is a variable length string. Field 2 and 3 is fixed length so I can split it. Another issue is I can do this on a script but we are wondering if we can split it on database level. If so, how. Or perhaps a link to a resource. Thanks

[kirk112]

This will give you col1 and col3, I will have a look at getting col2 to work later tonight

 

 

SELECT SUBSTRING_INDEX(data,' ',1) AS col1,

SUBSTRING_INDEX(data, '-', -1) AS col3

FROM table_name

[kirk112]

This works, there might be a better way of doing it, you just need to change data to the field name

 

SELECT SUBSTRING_INDEX(data,' ',1) AS col1,

LEFT(SUBSTRING_INDEX(data, ' ', -1),5) AS col2,

SUBSTRING_INDEX(data, '-', -1) AS col3

FROM table_name

 

let me know how you get on

[wsantos]

didn't work since the first column can have multiple whitespaces too.thanks

[wsantos]

thanks we can take it from here. Thanks again.