The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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
362151521
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.7k171174
accept rate: 24%

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
29.3k287438644
accept rate: 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 :-)

permanent link

answered 07 Nov '11, 11:53

Justin%20Willey's gravatar image

Justin Willey
6.4k101132197
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
362151521
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:

×9

question asked: 07 Nov '11, 05:52

question was seen: 809 times

last updated: 09 Nov '11, 03:03