Editing content of of fields

[ds2r]

Is it possible to edit the contents of fields after a select statemen? For example, I have a table Person (Id int, Username varchar(50)). Most of the rows in Username has the format of, say for example, aaa_01, bbb_02. I want to select this usernames with the underscores and the numeric parts ommited (Say for aaa_01, it will output aaa only). Is this possible in mysql?

Thanks guys.

[griswolf]

yes. You want the string funcctions: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html . You may need to deal with the substring stuff or, and the regular expression stuff, depending on exactly what you need and what data you have.

[tesuji]

Hi,
you may try this:

select 'aaa_01' as st, left(st, locate ('_', st)-1) as shorter
-- st       shorter
-------------------
-- aaa_01   aaa

-- however, code not tested. you should look-up left and locate functions from mysql manual whether
-- the parameters are in correct sequence (mysql has its own notion on how to put the parameters in 
-- sequence)

-- Also this should work (parameters are in correct sequence now for instr() had been copied 
-- from Oracle):

select 'aaa_01' as st, left(st, instr(st, '_')-1) as shorter

-- tesu

[ds2r]

@tesu

There was a bit of a problem in the syntax but after some fix it worked! Thank you very much :)