[SOLVED] Update / Replace Query

[Matrixkid]

Hi there,

I have a column with some fields that I need to switch around the way it is stored.

 

ex:

field: id

KILLERS, THE

USED, THE

PEOPLE, THE

MUSIC, THE

 

I want to run a query to make it into

THE KILLERS

THE USED

THE PEOPLE

THE MUSIC

 

So far ive got this far, but just need the end part.

UPDATE _testing set id = replace(id,'%, THE',

 

I am unclear as to how to finish this statement.

Thanks!

 

[fenway]

Mysql doesn't support capturing parens like php.  I suppose you could be sneaky like this:

 

UPDATE _testing set id = IF( 
LOCATE( REVERSE(', THE'), REVERSE(yourField) ) = 1
,CONCAT( 'THE ', REVERSE( SUBSTRING( REVERSE(yourField), LOCATE( REVERSE(', THE'), REVERSE(yourField) )+length(', THE') ) ) )
,yourField
)

[Matrixkid]

Mysql doesn't support capturing parens like php.  I suppose you could be sneaky like this:

 

UPDATE _testing set id = IF( 
LOCATE( REVERSE(', THE'), REVERSE(yourField) ) = 1
,CONCAT( 'THE ', REVERSE( SUBSTRING( REVERSE(yourField), LOCATE( REVERSE(', THE'), REVERSE(yourField) )+length(', THE') ) ) )
,yourField
)

 

 

Wow. thats just dirty. never thought of doing it like that.

 

thanks for the help! I appreciate it

[fenway]

No problem... enjoy!