[MSSQL '05] Characters uitfilteren tijdens replace*

[Verwijderd]

Hoi,

Ik ben momenteel bezig om een database op te schonen en consistent te maken. Met replace functie kom ik al een heel eind, alleen loop ik nu tegen een probleem.

In een kolom waar telnr in staan zie ik geregeld letters in staan. Is het mogelijk om alle letters eruit te filteren met replace:

update Tabel set Telefoon = replace (telefoon, 'a-zA-Z', '')

iets in die geest..

[Glabbeek]

Ik denk dat dit afhankelijk is van de databaseserver die je gebruikt. Welke gebruik je?

edit:

Hmmm, MS-SQL. Sorry, dan kan ik je niet verder helpen, daar weet ik weinig van af

[ Voor 36% gewijzigd door Glabbeek op 22-09-2006 13:09 ]

[Verwijderd]

MS SQL 2005

[Verwijderd]

ik denk ook niet dat dit mogelijk is middels query's. Een aantal php scripts zullen meer uitkomst brengen...

thx anyway

[0nline]

Verwijderd schreef op vrijdag 22 september 2006 @ 13:15:
ik denk ook niet dat dit mogelijk is middels query's. Een aantal php scripts zullen meer uitkomst brengen...

thx anyway

Je zou het met een cursor kunnen proberen.

(Je db ontwerp nalopen/wijzigen (rules/triggers/constraints) kan imho ook geen kwaad)

iig, suc6!

[P_de_B]

Als je nu een tabel letters maakt, met 1 kolom 'letter' en daarin alle letters van het alfabet zet kun je het volgende doen:

SQL:

WHILE EXISTS(SELECT * FROM Tabel WHERE ISNUMERIC(telefoon) = 0)
BEGIN
UPDATE Tabel
SET telefoon = REPLACE (t,l.letter, '')
FROM Tabel 
LEFT OUTER JOIN Letters L ON T.T LIKE '%' + L.Letter + '%'
END

Ik snap wel dat dit niet de meest elegante oplossing, gewoon even aangeven dat het op zich wel mogelijk is in SQL

[0nline]

ok hier dan, (ook niet de meest mooie script, maar moet volgens mij werken)

SQL:

SET NOCOUNT ON
--Declareer aantal variablen
DECLARE @OKStr VARCHAR(100),
    @OldTel VARCHAR(20),
    @NewTel VARCHAR(20),
    @Cnt INT,
    @RecID INT
    
--Toegestane karakters
SELECT @OKStr = '-0123456789'

--cursor 'maken'
DECLARE curTel CURSOR FOR
    SELECT RECORD_ID, TELEFOON FROM dbo.TabelNaam

--cursor open
OPEN curTel
--data ophalen
FETCH NEXT FROM curTel INTO @RecID, @OldTel

--loopen zolang je een record hebt/krijgt
WHILE (@@FETCH_STATUS = 0)
BEGIN
    --waardes resetten
    SELECT @NewTel = '', @Cnt = 0

    --Elke karakter onderzoeken
    WHILE ( @Cnt < LEN(@OldTel) )
    BEGIN
        --Als karakter voorkomt in je toegestane string
        IF CHARINDEX( UPPER(SUBSTRING(@OldTel, @Cnt, 1)), @OKStr, 0 ) > 0 -- Gevonden
            SELECT @NewTel = @NewTel + SUBSTRING(@OldTel, @Cnt, 1)
        SELECT @Cnt = @Cnt +1
    END
    --Exra check
    IF @NewTel <> ''
        UPDATE dbo.TabelNaam SET TELEFOON = @NewTel WHERE RECORD_ID = @RecID
    ELSE
        UPDATE dbo.TabelNaam SET TELEFOON = NULL WHERE RECORD_ID = @RecID --eventueel leeg maken

    --Volgend record ophalen
    FETCH NEXT FROM curTel INTO @RecID, @OldTel
END
--cursor sluiten en de-alloceren
CLOSE CurTel
DEALLOCATE CurTel

SET NOCOUNT OFF

hoop dat je er wat aan hebt

[Verwijderd]

Iedereen bedankt voor het antwoorden. Ik heb momenteel een php script gemaakt waarin ik middels reg ex de entrys doorloop.
Achteraf blijkt dat niet alleen texten in telefoon velden staan maar de manier van het noteren van de telefoonnummers is ook niet eenduidig.
+31... of +31...
012-3456789 of 012 3 456 879 of 012- 3-456-789
De gebruikers heir zijn erg creatief geweest.

[sopsop]

Verwijderd schreef op dinsdag 26 september 2006 @ 13:21:
[..]
De gebruikers heir zijn erg creatief geweest.

Nee de ontwikkelaar is lui en/of weinig professioneel geweest.

Als je een bepaalde opmaak van gebruikersinput wil hebben, dan moet je dat afdwingen. Je kunt er van uitgaan dat indien een gebruiker de mogelijkheid heeft om 'foutieve' data in te voeren dat er dan ook foutieve data wordt ingevoerd.

m.a.w. maak hier een eenmalige schoonmaak actie van en zorg dat de gebruikersinput in het goede formaat wordt afgedwongen.

[ Voor 11% gewijzigd door sopsop op 26-07-2007 12:04 ]