|
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, ' '); returns
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
Note: Both will fail if there are blanks "inside the column data", i.e. when data like "EA" might itself contain blanks. |
|
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. |