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 Vincent Buck |
There is no direct function to do this for you, but you can accomplish it using patindex and substr. Example:
gives
so wrapping it altogether you would use
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:
answered 06 Apr '10, 12:11 Mark Culp |
I think that Mark's solution is the cleanest, although I would make it more generic:
Is there a way to modify it to remove trailing chars? answered 06 Apr '10, 12:46 Jon Greisz |
You can use patindex to find the first character that is not the 0.
Thomas answered 06 Apr '10, 12:07 Thomas Dueme... |
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. answered 06 Apr '10, 12:05 Reimer Pods 1 minute ahead ;-) Yes, it looks like a number of us all answered at the same time :-) 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. |