on 2010 Apr 06 11:44 AM
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.
Request clarification before answering.
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;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You can use patindex to find the first character that is not the 0.
select patindex('%[^0]%','00000000383') -> 9
Thomas
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
52 | |
8 | |
5 | |
5 | |
5 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.