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.

[justin_willey]

I don't quite understand what it is you want to do, but if you need the two bits of the string seperated in a sql expression then this should do it:

create variable strval char(10);  
set strval = '1.0 EA';  
select left(strval,locate(strval,' ')-1) as Field1, right(strval,length(strval)-locate(strval,' ')) as Field2

(The first two lines are just for this illustration of course- replace strval in the select expression with whatever the actual field name is and add a FROM clause)

I'm sure one of the SQL gurus who haunt this sight will have a neater solution though 🙂