SAP SQL Anywhere

Question

Is there any string manipulation function which allows me to trim nonspace characters? I need to trim leading zeros from item codes, for example '000012KV' -> '12KV'. Converting to integer does not work because there are alphabtic characters in the item code field.

Accepted Answer

There is no direct function to do this for you, but you can accomplish it using patindex and substr. Example:

select '000012345' as s, patindex( '%[^0]%', s ) as i, substr( s, i ) as r

gives

s=000012345, i=5, r=12345

so wrapping it altogether you would use

set @trimmed_input = substr( @input, patindex( '%[^0]%', @input ) )

If you want, you can put this expression in a function and SQL Anywhere will automatically inline the function into your query (provided the function is a one-liner) when you use it:

CREATE FUNCTION trim_leading_zeros( in @input long varchar )
RETURNS long varchar
BEGIN
    RETURN substr( @input, patindex( '%[^0]%', @input ) );
END;

Answer 2

3

I think that Mark's solution is the cleanest, although I would make it more generic:

 CREATE FUNCTION ltrim_chars( IN acStr LONG VARCHAR, IN acChars LONG VARCHAR DEFAULT ' ')
    RETURNS LONG VARCHAR
 BEGIN
     RETURN SUBSTR( acStr, patindex( '%[^' || acChars || ']%', acStr ) );
 END;

Is there a way to modify it to remove trailing chars?

permanent link

answered 06 Apr '10, 12:46

Jon Greisz
571●9●7●9
accept rate: 0%

flat view

Answer 3

1	You can use patindex to find the first character that is not the 0. `select patindex('%[^0]%','00000000383') -> 9` Thomas permanent link answered 06 Apr '10, 12:07 Thomas Dueme... 2.7k●28●39●65 accept rate: 17% flat view

Answer 4

0

While this solution is not very elegant, it should give the desired result:

create function TrimZero (@str char(30))
returns char (30)
begin
  declare @pos integer;
  select @pos= patindex('%[^0]%',@str);
  if @pos = 0 then set @pos = 1 end if;

return substring (@str,@pos);
end

AFAIK SQLA 10 doesn't support regular expression which I would prefer to use in such a case.

permanent link

answered 06 Apr '10, 12:05

Reimer Pods
4.5k●38●48●91
accept rate: 11%

edited 06 Apr '10, 12:14

1 minute ahead ;-)

(06 Apr '10, 12:07) Thomas Dueme...

Yes, it looks like a number of us all answered at the same time :-)

(06 Apr '10, 12:12) Mark Culp

Just edited my post using patindex. IMHO Mark's solution might throw an error if the argument doesn't contain any '0', but I might be wrong.

(06 Apr '10, 12:19) Reimer Pods

flat view

SAP SQL Anywhere

Trimming leading zeros in SQL Anywhere 10

Follow this question

Related questions