REGEXP Question

#1

How can I use REGEXP to REPLACE characters in a string? Lets assume I
have a string like this: 'abc xyz 123 879-890' and I want to remove any
character that is NOT a number. The result should be '123879890'. In C#
it would be quite simple:

string s = Regex.Replace("abc xyz 123 879-890", @"[^0-9\+]", "");

Is there anything equivalent in SQL?? (I tried with the REPLACE()
function like

select replace ('abc xyz 123 879-890', regexp '[^0-9]', '')

but this gets me a syntax error.

Many thanks

Tom

#2

The SQL Standard contains the TRANSLATE_REGEX() function:

<regex transliteration> ::=
TRANSLATE_REGEX <left paren>
<XQuery pattern> [ FLAG <XQuery option flag> ]
IN <regex subject string>
[ WITH <XQuery replacement string> ]
[ FROM <start position> ]
[ USING <char length units> ]
[ OCCURRENCE <regex transliteration occurrence> ]
<left paren>

but SQL Anywhere does not support this function at this time.

Glenn

at wrote:

Quote:

How can I use REGEXP to REPLACE characters in a string? Lets assume I
have a string like this: 'abc xyz 123 879-890' and I want to remove any
character that is NOT a number. The result should be '123879890'. In C#
it would be quite simple:

string s = Regex.Replace("abc xyz 123 879-890", @"[^0-9\+]", "");

Is there anything equivalent in SQL?? (I tried with the REPLACE()
function like

select replace ('abc xyz 123 879-890', regexp '[^0-9]', '')

but this gets me a syntax error.

Many thanks

Tom

--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://case-express.sybase.com

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the
Sybase iAnywhere pages at
http://www.sybase.com/products/datab...chnicalsupport

#3

Thanks Glenn.

Tom

Glenn Paulley [Sybase iAnywhere] schrieb:

Quote:

The SQL Standard contains the TRANSLATE_REGEX() function:

regex transliteration> ::=
TRANSLATE_REGEX <left paren
XQuery pattern> [ FLAG <XQuery option flag> ]
IN <regex subject string
[ WITH <XQuery replacement string> ]
[ FROM <start position> ]
[ USING <char length units> ]
[ OCCURRENCE <regex transliteration occurrence> ]
left paren

but SQL Anywhere does not support this function at this time.

Glenn

at wrote:
How can I use REGEXP to REPLACE characters in a string? Lets assume I
have a string like this: 'abc xyz 123 879-890' and I want to remove
any character that is NOT a number. The result should be '123879890'.
In C# it would be quite simple:

string s = Regex.Replace("abc xyz 123 879-890", @"[^0-9\+]", "");

Is there anything equivalent in SQL?? (I tried with the REPLACE()
function like

select replace ('abc xyz 123 879-890', regexp '[^0-9]', '')

but this gets me a syntax error.

Many thanks

Tom

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

REGEXP Question

sybase.public.sqlanywhere.general sybase.public.sqlanywhere.general

REGEXP Question - 11-03-2009 , 11:51 AM

Re: REGEXP Question - 11-03-2009 , 12:18 PM

Re: REGEXP Question - 11-03-2009 , 01:07 PM