cancel
Showing results for 
Search instead for 
Did you mean: 

Trimming leading zeros in SQL Anywhere 10

Former Member
8,271

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 Solutions (1)

Accepted Solutions (1)

MarkCulp
Participant

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;

Answers (3)

Answers (3)

Former Member

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?

thomas_duemesnil
Participant

You can use patindex to find the first character that is not the 0.

select patindex('%[^0]%','00000000383') -> 9

Thomas

reimer_pods
Participant
0 Kudos

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.

thomas_duemesnil
Participant
0 Kudos

1 minute ahead 😉

MarkCulp
Participant
0 Kudos

Yes, it looks like a number of us all answered at the same time 🙂

reimer_pods
Participant
0 Kudos

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.