replace in mysql

hallo,

ich habe viele records mit telefon-nummer.

ich mochte finden the selbe nummer when eine neue user speichern seine
account:

ich habe diese sql:

select count(idperson) from person
where replace(replace(replace(replace(replace(trim(phone),'
',''),'/',''),'.',''),'-',''),',','') = '0415555555'

ist dass eine function für replace alle "non-number" ? ich habe nicht eine
"ereg_replace" function gefunden.

ich mochte gern: where remove_unnumber(phone) = '0415555555';

Danke.

Bob

Re: replace in mysql

Bob Bedford schrieb:
> hallo,
>
> ich habe viele records mit telefon-nummer.
>
> ich mochte finden the selbe nummer when eine neue user speichern seine
> account:
>
> ich habe diese sql:
>
> select count(idperson) from person
> where replace(replace(replace(replace(replace(trim(phone),'
> ',''),'/',''),'.',''),'-',''),',','') = '0415555555'
>
> ist dass eine function für replace alle "non-number" ? ich habe nicht eine
> "ereg_replace" function gefunden.
>
> ich mochte gern: where remove_unnumber(phone) = '0415555555';
>

If I understand you correctly, you're trying to remove all non-digits
from a phone number. If that's in fact what you're trying to do:
don't. Handle it in the application. These nested replace calls are
terribly difficult to read, and it's so much easier to remove
non-digits in the application with

s/\D//g (in Perl)

or

telephonenumber.replace(/\D/g,'') (in Javascript)

and I'm sure PHP has something similar to offer.

Re: replace in mysql

"Christian Kirsch" a écrit dans le message de news:
44abce06$0$29133$9b4e6d93@newsread4.arcor-online.net...
> Bob Bedford schrieb:
>> hallo,
>>
>> ich habe viele records mit telefon-nummer.
>>
>> ich mochte finden the selbe nummer when eine neue user speichern seine
>> account:
>>
>> ich habe diese sql:
>>
>> select count(idperson) from person
>> where replace(replace(replace(replace(replace(trim(phone),'
>> ',''),'/',''),'.',''),'-',''),',','') = '0415555555'
>>
>> ist dass eine function für replace alle "non-number" ? ich habe nicht
>> eine
>> "ereg_replace" function gefunden.
>>
>> ich mochte gern: where remove_unnumber(phone) = '0415555555';
>>
>
> If I understand you correctly, you're trying to remove all non-digits
> from a phone number. If that's in fact what you're trying to do:
> don't. Handle it in the application. These nested replace calls are
> terribly difficult to read, and it's so much easier to remove
> non-digits in the application with
>
> s/\D//g (in Perl)
>
> or
>
> telephonenumber.replace(/\D/g,'') (in Javascript)
>
> and I'm sure PHP has something similar to offer.
>
Thanks Christian,

The problem is that in the database, the numbers are already stored with
non-digits characters, that's my difficult. Maybe should I "format" this
field with a simple loop.

I'll probably do that.

Cheers...

Bob

Re: replace in mysql

Bob Bedford schrieb:
> "Christian Kirsch" a écrit dans le message de news:
> 44abce06$0$29133$9b4e6d93@newsread4.arcor-online.net...
>> Bob Bedford schrieb:
>>> hallo,
>>>
>>> ich habe viele records mit telefon-nummer.
>>>
>>> ich mochte finden the selbe nummer when eine neue user speichern seine
>>> account:
>>>
>>> ich habe diese sql:
>>>
>>> select count(idperson) from person
>>> where replace(replace(replace(replace(replace(trim(phone),'
>>> ',''),'/',''),'.',''),'-',''),',','') = '0415555555'
>>>
>>> ist dass eine function für replace alle "non-number" ? ich habe nicht
>>> eine
>>> "ereg_replace" function gefunden.
>>>
>>> ich mochte gern: where remove_unnumber(phone) = '0415555555';
>>>
>> If I understand you correctly, you're trying to remove all non-digits
>> from a phone number. If that's in fact what you're trying to do:
>> don't. Handle it in the application. These nested replace calls are
>> terribly difficult to read, and it's so much easier to remove
>> non-digits in the application with
>>
>> s/\D//g (in Perl)
>>
>> or
>>
>> telephonenumber.replace(/\D/g,'') (in Javascript)
>>
>> and I'm sure PHP has something similar to offer.
>>
> Thanks Christian,
>
> The problem is that in the database, the numbers are already stored with
> non-digits characters, that's my difficult. Maybe should I "format" this
> field with a simple loop.
>
> I'll probably do that.

After having thought more about it: Why do you even *want* to do that?
What's the problem of having telephone number stored as exactly the same
strings in the database as they were entered by the user?

For one thing, you must keep the '+' sign to indicate international
numbers. For another, formatting telephone numbers when outputting them
is tedious. And finally: *Not* formatting telephone numbers when
outputting them makes it more difficult for the users to read them.

Re: replace in mysql

> After having thought more about it: Why do you even *want* to do that?
> What's the problem of having telephone number stored as exactly the same
> strings in the database as they were entered by the user?
>
> For one thing, you must keep the '+' sign to indicate international
> numbers. For another, formatting telephone numbers when outputting them
> is tedious. And finally: *Not* formatting telephone numbers when
> outputting them makes it more difficult for the users to read them.

Hi Christian,

We have a commercial website for selling stuffs. Every account has a free
"slot" to sell what ever he wants, then others slots are paying. Until now
we managed to check the email every time a user created an account, but as
people are often cheating (and don't want to pay even if we have worked many
months on our website) they create many emails and then they still sell
others stuffs for free.

There is one thing that people can't create as easy as emails: phone
numbers. So instead of relying only on the email, we decided to check the
phone number: same phone number = same person, so we don't allow to create a
new username.

The problem is that until now we didn't filter what people entered in the
phone fields, so now we have to deal with finding any number entered by the
user checking against already entered phone numbers, that's why we need to
remove unwanted char for comparison only.

Bob