[SQL] filteren telefoonnummers

[Beyond]

Hallo,
Ik heb hier een database met ongeveer 7000 bestaande contactpersonen. Voor deze contactpersonen wordt onder andere het telefoonnummer, fax en mobiel nummer vastgelegd.
Nu wil ik voor een nieuw contactpersoon ,voordat ik 'm toevoeg, controleren of deze al niet toegevoegd is op basis van bv. telefoonnummer.

Nu is het zo dat de nummers in de database geen vaste format hebben. Ik wil graag alleen
de numerieke waarden uit het databaseveld hebben of ook controleren op "+", "-", " ", etc.

bv.
Invoer: 2345-6668
nu moet hij ook matchen met bv. "23456668" en/of 23+456668

Heb het volgende geprobeerd:

code:

SELECT MAINPHONE FROM ACCOUNT WHERE MAINPHONE LIKE '%2%3%4%5%6%6%6%8%'

maar die match ook op bv. 239456668 en dat wil ik juist niet.

en

code:

SELECT MAINPHONE FROM ACCOUNT WHERE MAINPHONE LIKE '[+- ()]2[+- ()]3[+- ()]4[+- ()]5[+- ()]6[+- ()]6[+- ()]6[+- ()]8[+- ()]'

maar dan gaat hij juist op zoek naar die pattern [+- ( )]

Wie heeft een oplossing?

[whoami]

Wat heeft dit met Tools en Environments te maken ?
Dit hoort gewoon in PRG thuis. Tools & Environments handelt over het gebruik en het werken met IDE's, etc...
Dit is gewoon programmeren.

-> PRG

[Verwijderd]

Ik zou, als ik jou was, de rotzooi als + en - etc gewoon niet in je database opslaan. Of, om performance redenen, een extra veld maken waar het nummer zonder al die meuk instaat..

[giMoz]

simpel

eerst zorgen dat beide zonder die meuk zijn:
dus

SQL:

SELECT * FROM TABLE
WHERE REPLACE(REPLACE(REPLACE(MAINPHONE,'-',''),'+',''),' ','') = REPLACE(REPLACE(REPLACE(@inputvariable,'-',''),'+',''),' ','')

edit:
het kan vast sneller met regexp meuk maar daar ben ik niet zo goed in..

[ Voor 38% gewijzigd door giMoz op 18-04-2006 16:25 ]

[Verwijderd]

Ik denk dat dat een graf graven is voor jezelf, giMoz.. de performance van die query zal naadje zijn.. eerst een uitgebreide replace en dan een like query.

Dat werkt waarsch. leuk als je 20 records in je database hebt, maar als dat er 20.000 worden kun je rustig vandaag je query opgeven en morgen terugkomen waarsch..

Gewoon een veld met de letterlijke user-input, wat alleen gebruikt wordt om weer te geven en in te voeren en dan een tweede veld waar het nummer instaat met vaste opmaak, bv. zonder overige tekens en links met nullen uitgevuld naar 11 posities (ja er bestaan 11-cijferige nummers).

Dan heb je geen rare replaces meer nodig en je kunt een nummer met 1 query zonder like en op index ophalen.

Dat is het verschil tussen 7000 bestaande contactpersonen in minder dan 1 seconde doorzoeken op een nummer met nauwelijks server-load, of 7000 bestaande contactpersonen doorzoeken in 20 minuten met een load van heb ik jou daar..

[ Voor 50% gewijzigd door Verwijderd op 18-04-2006 16:37 ]

[Beyond]

Die nummers staan al in een database. Dus die kan ik tijdens het invoeren niet meer in een 2de column (met vaste opmaak) drukken.

Hoe kan ik dat oplossen?

[Verwijderd]

Dat is een kwestie van een veldje toevoegen en eenmalig een scriptje draaien wat dat veldje vult aan de hand van je specs.

Ik zou een 'omzet-functie' maken in de code waar je mee werkt (php bv). Die doet niets anders dan louter de numerieke waardes uit een nummer filteren, dan uitvullen met 0-len tot een bepaalde lengte. Die functie kun je dan gebruiken in bovengenoemd scriptje (bouw je in 5 minuten in elkaar) en in de pagina waarop je nieuwe nummers toevoegt of bestaande wijzigt.

[Beyond]

Hmz,

Ok, zal het eens bekijken.

Zou het toch liever anders willen doen.... maar ja als het niet anders kan.

Thnks voor jullie replies.

[RobIII]

Maak een veldje extra in je tabel (foon_kaal ofzo ):

SQL:

Update MyTable Set foon_kaal = REPLACE(REPLACE(REPLACE(MyPhoneField,'-',''),'+',''),' ','')

Dan heb je je extra veldje al, en kun je voortaan bij het opslaan (lees: insert) het gestripte nummer meteen in dat veld zetten. Denk er ook aan dat je bij updates van bestaande gegevens dat veld bijwerkt.

Maar ergens is dit natuurlijk niet helemaal de bedoeling. Je kunt beter zorgen dat je een "vast formaat" hebt voor je telefoonnummers (en liefst helemaal zonder "opmaak") en het in je GUI pas "opmaken".

[djexplo]

Beyond schreef op dinsdag 18 april 2006 @ 16:16:
...

code:

SELECT MAINPHONE FROM ACCOUNT WHERE MAINPHONE LIKE '%2%3%4%5%6%6%6%8%'

maar die match ook op bv. 239456668 en dat wil ik juist niet.

Wie heeft een oplossing?

Met jou code, heb je in iedergeval maar een beperkt aantal nummers over. Als je deze met de code zoals b.v. die van Gimez door loopt heb je een best wel snelle oplossing.

Maar dan moet je iets van Select op een Sub-selection kunnen doen wil je dit geheel in SQL uitvoeren.