 |
 |
Archives of the TeradataForum
Message Posted: Wed, 29 Jun 2005 @ 05:22:36 GMT
| Subj: | | Re: Comma in string |
| |
| From: | | Dieter Noeth |
Ruth Alexander wrote
| | Utilizing Mike's comment, I wrote the following statement to count over from the first position to the comma, then dumped this in a 'city'
field, however this included the comma at the end. I put in a ', -1' after the Position function below (trying it both inside and outside the
parentheses at the end and all other ways I could think of), but it threw errors, complaining about the format. So I set a field equal to the
comma's position, named POS and then subtracted 1 from that and it worked well, but shouldn't the statement below work, with there being a way to
subtract out the comma with a -1 in the substr? | |
select substr(trim(address_Z), 1, position(',' in address_Z) - 1) from
sp.tl_116870_v4;
Better move the trim outside of the substring or you have to add it to the second address_Z, too:
select trim(substr(address_Z, 1, position(',' in address_Z) - 1)) from
sp.tl_116870_v4;
And even better use Standard SQL's substring :-)
select trim(substring(address_Z from 1 for position(',' in address_Z) - 1))
from sp.tl_116870_v4;
Dieter
| |
