Format Data

[Former Member]

Not sure if this is formatting or parsing but I would like to take this bit of data: '1.00 EA' and make it look like this: '1.00', 'EA'

In other words, separate the '1.00' from the 'EA'

I receive this field from a client who asks for a compare of data which requires separation of the above field. Currently through iSQL, I build a temp table and input the clients data which gives me '1.00 EA'. I then export that as a text file and import into excel as text, delimit the data and separate as such.

Any way I could do this directly through iSQL when creating a temp table without the exporting/importing?

Appreciate the help.

[jeff_albion · Accepted solution]

Which version of SQL Anywhere are you using?

(Note: For my answer, I am assuming you wish to break the single value '1.0 EA' in a column into two columns: '1.0' and 'EA').

I'm not sure how complicated the column's string formatting can get, but if you're using version 11 or 12, you can also use the more-flexible REGEXP_SUBSTR SQL function to grab non-white space characters at the beginning and end of the string:

create variable @strval char(10);  
set @strval = '1.0 EA';
select regexp_substr( @strval, '^\\S+' ) as c1,
   regexp_substr( @strval, '\\S+$' ) as c2;