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.
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;
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, ' ');
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, ' ');
Note: Both will fail if there are blanks "inside the column data", i.e. when data like "EA" might itself contain blanks.
answered 07 Nov '11, 12:32
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 :-)
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.
answered 09 Nov '11, 03:03