Question Re: Format Data in Technology Q&A

Format Data

Former Member — Mon, 07 Nov 2011 05:52:30 GMT

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.

Re: Format Data

justin_willey — Mon, 07 Nov 2011 11:53:54 GMT

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 🙂

Re: Format Data

VolkerBarth — Mon, 07 Nov 2011 12:32:57 GMT

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.

Re: Format Data

jeff_albion — Mon, 07 Nov 2011 12:49:35 GMT

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;

Re: Format Data

Former Member — Wed, 09 Nov 2011 03:03:34 GMT

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.