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.

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
22.3k9129262
accept rate: 40%

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.4k203357
accept rate: 16%

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.1k324278
accept rate: 12%

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:

×113
×90
×18

question asked: 06 Apr '10, 10:44

question was seen: 3,456 times

last updated: 06 Apr '10, 15:52