Replace-Function with Sybase?

[Alexander Rechsteiner]

Hi everybody

I have the following Problem(s):

As search-expression, I do have a Number with 11 digits (like 12345678909)
as varchar. It can start with some 0 (like 00001234567).
Now, the attribute I need to query for is a string with 30 Digits.

Problem Nr 1:

The "Numbers" (varchar(30)) on the DB are stored like this:

12.345.67
00-123-456-7
1-2-3-4-5-6-7/L
123.456.7 BLAHBLAH

Means: Its just in the DB somehow, surrounded by dots, -es, slashes, chars
or other crap.

So on, i need to compare a number like 00001234567 with a varchar(30) on the
DB, which is maybe formated like "12.345.BLABLA.67".

Problem Nr 2:

I tried to use the Replace function for something like:

SELECT REPLACE('12.345.BLABLA.67','[^0-9]','')
go;


Or, for expamle:

SELECT REPLACE('hello world','world','sunshine')
go;

Now, the use of replace with this syntax causes an error:

" Incorrect syntax near the keyword 'REPLACE'. "

But I think the syntax is correct, and if the function wouldn't be around,
it would sound like:

"Function 'REBLASE' not found. If this is a SQLJ function, use sp_help to
check whether the object exists (sp_help may produce a large amount of
output)."

Help.

Alexander

[b***@sybase.com]

Post by Alexander Rechsteiner
Hi everybody
As search-expression, I do have a Number with 11 digits (like 12345678909)
as varchar. It can start with some 0 (like 00001234567).
Now, the attribute I need to query for is a string with 30 Digits.
12.345.67
00-123-456-7
1-2-3-4-5-6-7/L
123.456.7 BLAHBLAH
Means: Its just in the DB somehow, surrounded by dots, -es, slashes, chars
or other crap.
So on, i need to compare a number like 00001234567 with a varchar(30) on the
DB, which is maybe formated like "12.345.BLABLA.67".
SELECT REPLACE('12.345.BLABLA.67','[^0-9]','')
go;
SELECT REPLACE('hello world','world','sunshine')
go;
" Incorrect syntax near the keyword 'REPLACE'. "
But I think the syntax is correct, and if the function wouldn't be around,
"Function 'REBLASE' not found. If this is a SQLJ function, use sp_help to
check whether the object exists (sp_help may produce a large amount of
output)."
Help.
Alexander

That would be true if REPLACE wasn't a keyword - if a possible token
isn't
recognized, ASE assumes it could be a procedure name and raises that
error. But since REPLACE is a known token, just not used correctly,
you
get the syntax error.

Sounds like you want the STR_REPLACE function.

-bret

[Alexander Rechsteiner]

Hi Bret

Post by b***@sybase.com
Sounds like you want the STR_REPLACE function.

Yes. But this Function doesn't work as i expected. Can you tell me how to
cut everything out of a string, that is not a numeric-digit between 0 and 9?

I tried the following with a pattern matching operator:

SELECT STR_REPLACE("abc99922defg121212hicde","[^0-9]","")
go;

But the result is still "abc99922defg121212hicde" :-(

Further, if I tried to replace " " with "" it does nothing, too.
For example:

SELECT STR_REPLACE("abc defg121212hicde"," ","")
go;

Result:

"abc defg121212hicde"

The first example with replace-String "x" instead of "" doesn't work, too.

Hmmmm....

Alexander

[Rob Verschoor]

You cannot use patterns in str_replace()...

HTH,

Rob V.
-------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
and Replication Server 12.5 / TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE" (new edition!)
"The Complete Sybase ASE Quick Reference Guide" (new edition!)
"The Complete Sybase Replication Server Quick Reference Guide"

mailto:***@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
-------------------------------------------------------------

Post by Alexander Rechsteiner
Hi Bret

Post by b***@sybase.com
Sounds like you want the STR_REPLACE function.

Yes. But this Function doesn't work as i expected. Can you tell me how to
cut everything out of a string, that is not a numeric-digit between 0 and 9?
SELECT STR_REPLACE("abc99922defg121212hicde","[^0-9]","")
go;
But the result is still "abc99922defg121212hicde" :-(
Further, if I tried to replace " " with "" it does nothing, too.
SELECT STR_REPLACE("abc defg121212hicde"," ","")
go;
"abc defg121212hicde"
The first example with replace-String "x" instead of "" doesn't work, too.
Hmmmm....
Alexander

[Jochen Schug]

On Thu, 07 Sep 2006 08:46:41 +0200, Alexander Rechsteiner =

Yes. But this Function doesn't work as i expected. Can you tell me how=

to

cut everything out of a string, that is not a numeric-digit between 0 =

=

and 9?
SELECT STR_REPLACE("abc99922defg121212hicde","[^0-9]","")
go;
But the result is still "abc99922defg121212hicde" :-(

The only solution I see involves looping over the varchar and throw =

everthing out that's not a digit, e.g.

set nocount on
declare @a varchar(20), @p int
select @a =3D '123A-fdsf?ask?/45x'

select @a

select @p =3D patindex('%[^0-9]%', @a)
while @p > 0
begin
select @a =3D left(@a, @p-1) + substring(@a, @p+1, len(@a))
select @p =3D patindex('%[^0-9]%', @a)
end

Further, if I tried to replace " " with "" it does nothing, too.
SELECT STR_REPLACE("abc defg121212hicde"," ","")
go;
"abc defg121212hicde"
The first example with replace-String "x" instead of "" doesn't work, =

=

too.

Try to replace the blank with NULL.


Jochen