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.

asked 06 Apr '10, 10:44

Sergio's gravatar image

Sergio
460151820
accept rate: 0%

edited 06 Apr '10, 15:52

Vincent%20Buck's gravatar image

Vincent Buck
70681520


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

answered 06 Apr '10, 12:11

Mark%20Culp's gravatar image

Mark Culp
24.9k10141297
accept rate: 41%

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%20Greisz's gravatar image

Jon Greisz
571979
accept rate: 0%

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%20Duemesnil's gravatar image

Thomas Dueme...
2.7k293965
accept rate: 17%

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%20Pods's gravatar image

Reimer Pods
4.5k384891
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
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:

×119
×90
×22

question asked: 06 Apr '10, 10:44

question was seen: 7,580 times

last updated: 06 Apr '10, 15:52