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.

asked 07 Nov '11, 05:52

zippidydo's gravatar image

zippidydo
377151521
accept rate: 0%

edited 07 Nov '11, 05:55


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;
permanent link

answered 07 Nov '11, 12:49

Jeff%20Albion's gravatar image

Jeff Albion
10.8k171175
accept rate: 25%

edited 07 Nov '11, 14:08

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.

permanent link

answered 07 Nov '11, 12:32

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

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 :-)

permanent link

answered 07 Nov '11, 11:53

Justin%20Willey's gravatar image

Justin Willey
7.6k137179249
accept rate: 20%

edited 07 Nov '11, 11:54

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.

permanent link

answered 09 Nov '11, 03:03

zippidydo's gravatar image

zippidydo
377151521
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×10

question asked: 07 Nov '11, 05:52

question was seen: 2,555 times

last updated: 09 Nov '11, 03:03