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.

[Former Member]

Copied the example in the documentation (link provided by Jeff) and it did the trick:

Example The following example breaks values in the Employees.Street column into street number and street name:

SELECT REGEXP_SUBSTR( Street, '^S+' ) as street_num, REGEXP_SUBSTR( Street, '(?<=^S+s+).*$' ) AS street_name FROM Employees;

This is what I was looking for. Saved one step in the process.

Appreciate all the help and suggestions.