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.

[VolkerBarth]

Like Justin, I don't understand your requirements.

Generally it's easiest if you get the data already with the required delimiter. Possibly you can choose a fitting delimiter when the data is exported by your customer...

---

However, if you just want to replace a blank with another delimiter, you can also use the replace() function, i.e. to replace the blank with a comma something like

select replace(strval, ' ', ',');

Or you could use the sa_split_list() procedure to build a result set containing each blank-separated text as one single row:

call sa_split_list(strval, ' ');

returns

line_num row_value
1 1.0
2 EA

To recombine this, you can use the LIST aggregate, such as

select list(row_value, ',' order by line_num) as MyNewRow
from sa_split_list(strval, ' ');

returns

MyNewRow
1.0,EA

Note: Both will fail if there are blanks "inside the column data", i.e. when data like "EA" might itself contain blanks.