Dziś jest to ostatni odcinek serialu pt. "rozdzielanie nazw ulic". Mam nadzieję, że będzie budził zainteresowanie i żywą dyskusję.
Zanim zacznę podawać rozwiązania problemów, chciałbym zaznaczyć, że nie wszystko udało mi się rozwiązać, ponieważ są przypadki w których zależy to od interpretacji gdzie jest numer domu
a gdzie jest numer mieszkania
jak w komentarzach powyżej. Chodzi o to, że są przypadki w których niezwykle ciężko jest określić co jest numerem domu, a co numerem mieszkania jak podał @BraVolt na przykładzie adresu Marszałkowska 104/122
- tutaj wszystkie numery są określone jako numer budynku bez podziału na coś takiego jak numer mieszkania, co w konsekwencji będzie błędem przy rozdzielaniu tego typu adresów gdyż 122
zostanie zakwalifikowane jako "numer mieszkania" i tego niestety nie da się uniknąć przy hurtowym
rozdzielaniu adresów.
Dla uproszczenia pominąłem też wiele rodzajów zapisów adresów gdyż nie są one jakoś ustandaryzowane i pominąłem przedrostki "ul", "al" i takie zapisy jak niżej nie były przeze mnie brane pod uwagę:
- ul Jagodowa 14 m 5
- ul. Jagodowa 14/m5
- al. Zbyszka z Bogdańca 14/123/234
- Dywizjonu 303 20/30a/1
itp... ponieważ tych kombinacji byłoby od %$^#
W swoich rozważaniach do rozdzielania nazw ulic przyjąłem takie przykłady jak - oto kompletna lista
1 sierpnia 20
1 sierpnia 20/30
1 sierpnia 20a/30
1 sierpnia 20/30a
1 sierpnia 20/30a 7
1 sierpnia 20 7
1 sierpnia 20 m7
Kwiatowa 20
Kwiatowa 20a
Kwiatowa 20/30
Kwiatowa 20a/30
Kwiatowa 20/30a
Kwiatowa 20/30 2
Kwiatowa 20a/30 2
Kwiatowa 20/30a 2
Kwiatowa 20/20 m2
Dywizjonu 303 20
Dywizjonu 303 20/30
Dywizjonu 303 20a/30
Dywizjonu 303 20/30a
Dywizjonu 303 20/30 1
Dywizjonu 303 20a/30 1
Dywizjonu 303 20/30a 1
Bitwy Warszawskiej 1920 30
Bitwy Warszawskiej 1920 20/30
Bitwy Warszawskiej 1920 20a/30
Bitwy Warszawskiej 1920 20/30a
Bitwy Warszawskiej 1920 20 8
Bitwy Warszawskiej 1920 20a/30 8
Bitwy Warszawskiej 1920 20/30a 8
gen. Jakiegoś Tam 30
gen. Jakiegoś Tam 30a
gen. Jakiegoś Tam 30/20
gen. Jakiegoś Tam 30a/20
gen. Jakiegoś Tam 30/20a
gen. Jakiegoś Tam 30a/20 5
gen. Jakiegoś Tam 30/20a 5
Astronautów kosmosu 10
Astronautów kosmosu 10/20
Astronautów kosmosu 10a/20
Astronautów kosmosu 10/20a
Astronautów kosmosu 10/20 7
Astronautów kosmosu 10/20a 7
Astronautów kosmosu 10a/20 7
Rondo czegoś tam 1
Rondo czegoś tam 1/15
Rondo czegoś tam 1a/15
Rondo czegoś tam 1/15a
Rondo czegoś tam 1/15 8
Rondo czegoś tam 1a/15 8
Rondo czegoś tam 1/15a 8
28 lipca 1943 2
28 lipca 1943 2/12
28 lipca 1943 2a/12
28 lipca 1943 2/12a
28 lipca 1943 2/12 2
28 lipca 1943 2a/12 2
28 lipca 1943 2/12a 2
Byłych Więźniów Twierdzy Zakroczymskiej 11
Byłych Więźniów Twierdzy Zakroczymskiej 11/15
Byłych Więźniów Twierdzy Zakroczymskiej 11a/15
Byłych Więźniów Twierdzy Zakroczymskiej 11/15a
Byłych Więźniów Twierdzy Zakroczymskiej 11/15 2
Byłych Więźniów Twierdzy Zakroczymskiej 11a/15 2
Byłych Więźniów Twierdzy Zakroczymskiej 11/15a 2
Chciałbym jeszcze wspomnieć, że przy pełnym adresie takim jak Dywizjonu 303 20/30a 1
przyjąłem założenie, że numerem mieszkania będzie 1
a poprzedzająca część 20/30a
będzie numerem domu, aby uniknąć i tym samym częściowo rozwiązać problem z adresem typu Marszałkowska 104/122
w którym część liczbowa określa tylko budynek.
Do rozwiązania problemu użyłem wyrażeń regularnych REGEX i pracowałem na silniku bazodanowym MariaDB
(xampp).
Zanim zaczniemy cokolwiek dalej robić, to polecam zrobienie kopii zapasowej tabeli która zawiera pełne dane adresowe. Jak to zrobić ?
1. Tworzymy nową kolumnę obok kolumny źródłowej
ALTER TABLE `tabela2` ADD COLUMN `pAdresCopy` varchar(100) CHARACTER SET utf8 COLLATE utf8_polish_ci AFTER `pAdres`
2. Robimy kopię zapasową tabeli źródłowej
UPDATE `tabela2` SET `pAdresCopy`=`pAdres`
Jak już mamy zrobioną kopię kolumny z adresami, to polecam całą robotę wykonać na kopii zapasowej.
Ja u siebie robiłem na oryginale, gdyż po prostu cała baza była testowa i nie miałem tam żadnych istotnych danych które mógłbym przez nieuwagę zmienić.
Chcąc rozdzielić nazwy ulic przyjrzałem się ich wszystkich znakom nawet tym białym, ponieważ ułatwiają one rozwiązanie problemu na części składowe typu ulica
, numer domu
i numer mieszkania
, a więc do rzeczy.
Na początku, trzeba WYDZIELIĆ WSZYSTKIE nazwy ulic typu -> Kwiatowa
, Dywizjonu 303
, Bitwy Warszawskiej 1920
, 28 lipca 1943
, 1 sierpnia
, 1 Maja
etc... i selekcjonuję wszystkie potencjalne nazwy wyrażeniem regularnym które jest przedstawione w pkt. 1
Podczas pisania tego posta, odkryłem, że problemem może być nazwa ulicy np Jana Pawła 2
chociaż wydaje mi się, że powinno być to zapisane jako Jana Pawła II
poniższe wyrażenie radzi sobie z drugą formą zapisu, natomiast z pierwszą już nie.
1. Wyrażenie regularne wygląda tak
^\S*\b(\D*[^\d\s\/])\b\s\d[^\/]\d{1,4}\s|^\S*\b(\D*[^\d\s\/])\b
2. Zapytanie SQL wygląda tak
SELECT `pAdres`,
REGEXP_SUBSTR(`pAdres`,'^\\S*\\b(\\D*[^\\d\\s\\/])\\b\\s\\d[^\\/]\\d{1,4}\\s|^\\S*\\b(\\D*[^\\d\\s\\/])\\b') AS 'Ulica'
FROM `tabela2`
3. A wyniki zapytania SQL wyglądają tak
Skoro wiemy jak będą wyglądały wyniki po rozdzieleniu, to czas na załadowanie wyników do kolumny pUlica
. Zapytanie które to robi wygląda jak niżej
UPDATE `tabela2`
SET
`pUlica`= REGEXP_SUBSTR(`pAdres`,'^\\S*\\b(\\D*[^\\d\\s\\/])\\b\\s\\d[^\\/]\\d{1,4}\\s|^\\S*\\b(\\D*[^\\d\\s\\/])\\b')
Po wykonaniu tego polecenia nazwy samych ulic powinny zapisać się w kolumnie pUlica
(zamieszczam tylko fragment wyników)
teraz przyszedł czas na rozdzielenie numerów domów
od numerów mieszkań
- jak wspomniałem na początku, w pewnych przypadkach należy się pogodzić z tym, że numer budynku będzie rozdzielony
na numer budynku
i na numer mieszkania
...
Zauważyłem, że są cztery rodzaje numerów budynków i numerów mieszkań (jeżeli chodzi o przypadki które uwzględniłem do rozwiązania problemu i rozdzielania pełnego adresu na poszczególne składowe)
-
20
- po prostu sam numer budynku
-
20 5
- numer budynku i numer mieszkania, ten przypadek jest problematyczny, zważywszy na to, jak wystąpi w takiej postaci jak Dywizjonu 303 20 5
- wtedy trzeba uciekać się do sztuczki ze znakiem podkreślenia _ w taki sposób, aby adres wyglądał jak jeden długi string i miał na końcu części numerycznej, która zawiera się nazwie ulicy, ten znak, aby łatwiej było wyciągnąć numer budynku np Dywizjonu_303_20 5 i mamy już wzorzec dzięki któremu już łatwo oznaczyć numer domu/budynku
czyli **_20 ** - ZA liczbą 20 jest "spacja"
-
20/30
- numer budynku i numer mieszkania, ten przypadek również jest problematyczny w zależności od kontekstu jak wspominałem na początku (dla uproszczenia pomijam tutaj literę dla budynku lub dla mieszkania np 20a/30
lub 20/30a
może być i 20a/30a
)
-
20/30 5
- numer budynku i numer mieszkania
rozdzielenie
numeru budynku od numeru mieszkania będzie polegać na znalezieniu jakiegoś łącznika np ukośnika / lub białego znaku tj. "spacji" oraz wykorzystaniu go do odseparowania z lewej lub z prawej strony i usunięcia go (o co chodzi, to zobaczycie dalej).
##Rozdzielanie numeracji z pkt. 1.
W całym adresie np Kwiatowa 20 szukamy liczby która występuje na końcu - niech będzie to jak w przykładzie 20. Tą liczbę interpretuję jako numer mieszkania
, ponieważ wskazuje nam bezpośrednio lokal do którego należy się udać - może to być dom jednorodzinny bądź mieszkanie w bloku. Poniżej podaję wyrażenie regularne do oznaczenia szukanego wzorca. W znalezieniu tej liczby pomocne są następujące szczegóły - liczba znajduje się na samym końcu adresu i przed tą liczbą występuje "spacja".
1. Wyrażenie regularne które szuka takich liczb to - UWAGA, przed \d
jest "spacja"
\d{1,4}$| m\d{1,4}$| \d{1,4}[a-zA-Z]$| m\d{1,4}[a-zA-Z]
2. Zapytanie SQL
SELECT `pAdres`, REGEXP_SUBSTR(`pAdres`,' \\d{1,4}$| m\\d{1,4}$| \\d{1,4}[a-zA-Z]$| m\\d{1,4}[a-zA-Z]') AS 'Numer mieszkania'
FROM `tabela2`
3. Wyniki zapytania (dla uproszczenia podaję częściowy wynik)
4. Przyszedł czas do załadowania numerów mieszkania do kolumny pNr_mieszkania
UPDATE `tabela2`
SET
`pNr_mieszkania` = REGEXP_SUBSTR(`pAdres`,' \\d{1,4}$| m\\d{1,4}$| \\d{1,4}[a-zA-Z]$| m\\d{1,4}[a-zA-Z]')
5. Rezultat (tylko częściowe wyniki)
Rozdzielenie numeracji z pkt. 2
Ten punkt polecam wykonać na samym końcu, gdyż jest on jednym z najtrudniejszych etapów i mogą być problemy z rozdzieleniem numerów domów
od numerów mieszkań
. Przed przystąpieniem do realizacji tej części wykonaj dodanie gwiazdek * z punktu 3 lub 4.
Weźmy na warsztat pełny adres taki jak Dywizjonu 303 20 5
lub Bitwy Warszawskiej 1920 20 5
. Tutaj naszym zadaniem będzie oznaczenie pełnej nazwy ulicy w taki sposób aby to był jeden string który zawiera widoczne znaki (zdaję sobie sprawę, że "spacja" też jest znakiem ale bardziej chodzi mi o to, aby były pewne różnice w odpowiednich miejscach i tym samym ułatwić sobie robotę w rozdzieleniu właściwego numeru budynku od pełnej nazwy ulicy). Przytoczone przykłady ulic doprowadzamy do takiej postaci jak niżej:
a) Dywizjonu_303_20 5
b) Bitwy_Warszawskiej_1920_20 5
c) Kwiatowa_20 5
Dlaczego tak ? No w sumie tak mi przyszło do głowy bo taka fantazja mnie naszła :D a po drugie, wystarczy jedno krótkie wyrażenie regularne które wyszuka nam według wzorca znak _ potem liczbę, a następnie spację lub inny znak np # który znajduje się za tą liczbą i już mamy oznaczony numer budynku ;) wtedy mamy bardzo ułatwione zadanie i pozostanie nam tylko oddzielenie numeru domu
od pełnej nazwy ulicy.
Żeby nie kopać się z pojedynczym wklejaniem zapytania po to, aby dodać podkreślniki w każdym rekordzie znajdującym się w tabeli, to utworzyłem funkcję która to robi automatycznie. Nie było łatwo ale warto było nad tym posiedzieć. Nie będę tłumaczył co oznaczają poszczególne fragmenty kodu, bo artykuł byłby zbyt długi ale w komentarzach zawarłem najistotniejsze informacje. Poniżej funkcja:
Funkcja przyjmuje jeden parametr typu INT jest to ID rekordu który trzeba zmienić
1. Funkcja SQL która dodaje znak _ (najpierw sprawdzamy czy zapytanie zadziała właściwie wywołując ją z jednym parametrem który przyjmuje)
DELIMITER //
CREATE FUNCTION ZmienWybranyTekst(id int) -- nazwa funkcji wraz z argumentem który podajesz w wywołaniu
RETURNS int DETERMINISTIC READS SQL DATA
BEGIN
DECLARE DoZmiany varchar(100) DEFAULT '';
DECLARE WydobytaWartosc varchar(100) DEFAULT '';
DECLARE WstawianieWartosci varchar(100) DEFAULT '';
DECLARE WstawianieWartosci2 varchar(100) DEFAULT '';
DECLARE PoZmianie varchar(100) DEFAULT '';
SET DoZmiany=(SELECT `pAdres` FROM `tabela2` WHERE `pID`=id);
SET WydobytaWartosc=(REGEXP_substr(DoZmiany,'^\\S*\\b\\D*[^\\d\\/]\\b\\d[^\\/]\\d{1,4}|^\\S*\\b\\D*[^\\d\\/ ]\\b'));
SET WstawianieWartosci=(REGEXP_replace(WydobytaWartosc,'\\b \\b','_'));
SET WstawianieWartosci2=(rpad(mid(WstawianieWartosci,1,char_length(WstawianieWartosci)),char_length(WstawianieWartosci)+1,'_'));
SET PoZmianie=(REGEXP_replace(DoZmiany,'\\S*\\b\\D*[^\\d\\/]\\b\\d[^\\/]\\d{1,4} |^\\S*\\b\\D*[^\\d\\/ ]\\b ',WstawianieWartosci2));
UPDATE `tabela2` SET `pAdres`=PoZmianie WHERE `pID`=id; -- te zapytanie musisz dostosować do swojej tabeli
RETURN 0;
END; //
DELIMITER ;
Jeżeli chcesz tą funkcję zaadaptować do swojej tabeli, to musisz w zapytaniu UPDATE
zmienić nazwę tabeli i kolumny na swoją. Następnie należy kliknąć przycisk WYKONAJ
w xampp
2. Wywołanie funkcji z jednym parametrem
SELECT ZmienWybranyTekst(55) AS 'Wywołanie funkcji';
3. Kilka przykładowych poleceń które ułatwią zarządzanie funkcjami
a) wywołanie funkcji
SELECT nazwa_funkcji();
b) sprawdzenie statusu funkcji
SHOW FUNCTION STATUS;
c) usuwanie funkcji - to jest opcjonalne i przydaje się w sytuacji gdy funkcja nie jest już potrzebna
DROP FUNCTION nazwa_funkcji;
Powyższa funkcja wywołana jest tylko JEDEN RAZ - w sumie mogłem napisać jedną która wywoła się setki razy ale musiałem się nauczyć pisać te funkcje i napisałem drugą funkcję która wywoła powyższą setki razy tj. tyle razy ile jest rekordów w bazie.
DELIMITER //
CREATE FUNCTION UruchomZmianeTekstu() -- nazwa funkcji która nie przyjmuje argumentów
RETURNS int DETERMINISTIC MODIFIES SQL DATA
BEGIN
DECLARE done int DEFAULT false;
DECLARE i int DEFAULT 0;
DECLARE id int DEFAULT 0;
DECLARE cur CURSOR FOR SELECT `pID` FROM `tabela2`;
DECLARE CONTINUE HANDLER FOR NOT found SET done=true;
OPEN cur;
petla: LOOP
FETCH cur INTO id;
IF(done=true) THEN
LEAVE petla;
ELSE
SET i=(SELECT ZmienWybranyTekst(id)); -- tutaj jest wywołanie funkcji która dodaje znaki _
END IF;
END LOOP;
CLOSE cur;
RETURN i;
END; //
DELIMITER ;
4. Wywołanie funkcji która doda znaki _ do wszystkich rekordów
SELECT UruchomZmianeTekstu() AS 'Wywolanie funkcji';
5. Rezultat tego zapytania (tylko częściowy wynik)
No i fajnie wyszło :D
6. Rozdzielenie numerów domów od pełnego adresu zapytaniem SQL (wykorzystując znak _ i "spację" między liczbą "_20 ")
SELECT `pAdres`,
REGEXP_SUBSTR(`pAdres`,'_\\d{1,4} ') AS 'Numer domu'
FROM
`tabela2`
7. Rezultat zapytania
8. Załadowanie numerów domów do właściwej kolumny zapytaniem SQL
UPDATE `tabela2`
SET `pNr_domu`=REGEXP_SUBSTR(`pAdres`,'_\\d{1,4} ')
WHERE `pNr_domu` IS null;
9. Rezultat zapytania SQL
10. Usunięcie znaków _ i ewentualnych spacji które na pewno są (wystarczy zamiast znaku _ wpisać znak "spacji")
UPDATE `tabela2`
SET `pNr_domu`= REPLACE(`pNr_domu`,'_','')
11. Rezultat
I już wszystko jest rozdzielone, tak jak powinno być.
Jeżeli ten punkt został wykonany na samym końcu, to można usunąć skopiowaną kolumnę.
ALTER TABLE `tabela2` DROP COLUMN `pAdresCopy`
Rozdzielanie numeracji z pkt. 3
Tutaj szukamy numeru domu
z zapisu 20/30
lub 20a/30
lub 20/30a
lub 20a/30a
i pojawiają się tutaj schody, ponieważ w tym przypadku szukamy czegoś co jest PRZED znakiem / tj. 20/
itp...
Wyrażenie regularne które chcemy użyć, zaznaczy nam wszystko co do tego wzorca pasuje, nawet część z zapisu 20/30 5
gdzie 20/30
to numer budynku, a 5
to jest numer domu. A w tym przypadku tego nie chcemy.
Jak uniknąć takiej pułapki ? Odpowiemy w następnym odcinku :D (żart).
Wpadłem na pomysł aby napisać wyrażenie regularne które obejmie TYLKO zapis 20/30 5
. I tam gdzie występują te zapisy, to za pomocą zapytania SQL dodam w jakieś wygodne miejsce np "gwiazdkę" aby te wpisy nam się nie pomieszały i było łatwiej wyselekcjonować wyrażeniem regularnym to, co nas interesuje.
1. Wyrażenie regularne które oznacza nam numeracje w adresie według pożądanego wzorca to:
\d{1,4}[a-zA-Z][\/]\d{1,4} \d{1,3}|\d{1,4}[\/]\d{1,4}[a-zA-Z] \d{1,3}|\d{1,4}[\/]\d{1,4} \d{1,3}|\d{1,4}[\/]\d{1,4} m\d{1,3}|\d{1,4}[\/]\d{1,4}\/\d{1,3}
2. Zapytanie SQL
SELECT `pAdres`,
REGEXP_SUBSTR(`pAdres`,'\\d{1,4}[a-zA-Z][\\/]\\d{1,4} \\d{1,3}|\\d{1,4}[\\/]\\d{1,4}[a-zA-Z] \\d{1,3}|\\d{1,4}[\\/]\\d{1,4} \\d{1,3}|\\d{1,4}[\\/]\\d{1,4} m\\d{1,3}|\\d{1,4}[\\/]\\d{1,4}\\/\\d{1,3}') AS 'Numer'
FROM
`tabela2`
3. Rezultaty zapytania
Wiedząc, że wyrażenie regularne oznacza rekordy które chcemy, to czas je zamienić na */ a w tym celu użyjemy zapytania SQL
4. Zapytanie SQL
UPDATE `tabela2`
SET
`pAdres`= REPLACE(`pAdres`,'/','*/')
WHERE
LENGTH(REGEXP_SUBSTR(`pAdres`,'\\d{1,4}[a-zA-Z][\\/]\\d{1,4} \\d{1,3}|\\d{1,4}[\\/]\\d{1,4}[a-zA-Z] \\d{1,3}|\\d{1,4}[\\/]\\d{1,4} \\d{1,3}|\\d{1,4}[\\/]\\d{1,4} m\\d{1,3}|\\d{1,4}[\\/]\\d{1,4}\\/\\d{1,3}'))
5. Rezultat
Po oznaczeniu adresów które chcemy wykluczyć z wyszukiwania wyrażeniem regularnym mamy już ułatwioną drogę wyselekcjonowania tego co chcemy.
6. Wyrażenie regularne które to robi
\d{1,4}[\/]|\d{1,4}[a-zA-Z][\/]
7. Zapytanie SQL
SELECT `pAdres`, REGEXP_SUBSTR(`pAdres`,'\\d{1,4}[\\/]|\\d{1,4}[a-zA-Z][\\/]') AS 'Numer domu' FROM `tabela2`
8. Rezultat zapytania (częściowe wyniki)
Z tak wyselekcjonowanym numerem można przystąpić do załadowania tego numeru do kolumny zawierającej numer domu
9. Załadowanie rozdzielonych liczb do kolumny pNr_domu
UPDATE `tabela2`
SET `pNr_domu`=REGEXP_SUBSTR(`pAdres`,'\\d{1,4}[\\/]|\\d{1,4}[a-zA-Z][\\/]')
10. Rezultat (częściowe wyniki)
11. Usunięcie / zapytaniem SQL z kolumny pNr_domu
UPDATE `tabela2`
SET
`pNr_domu`= REPLACE(`pNr_domu`,'/','')
WHERE
LENGTH(REGEXP_SUBSTR(`pNr_domu`,'\\d{1,4}[\\/]|\\d{1,4}[a-zA-Z][\\/]'))
12. Wyniki zapytania (częściowy wynik)
Teraz zaczniemy rozdzielać numer mieszkania
od numeru domu
i w kolejnych punktach numer mieszkania
zostanie przeniesiony do kolumny pNr_mieszkania
. Do tego celu użyjemy wyrażenia regularnego które wyszukuje nam wszystko co znajduje się ZA znakiem / czyli /30
, /30a
itp... - ale UWAGA, tutaj też nie chcemy aby to wyrażenie uwzględniło nam wszystkie rekordy zawierające cokolwiek ZA znakiem / gdyż jest taki zapis jak 20/30 7
- tutaj też dodamy gwiazdkę w dogodne miejsce. Więc zrobimy to następującym zapytaniem SQL.
13. Zapytanie SQL
UPDATE `tabela2`
SET
`pAdres`= REPLACE(`pAdres`,'*/','*/*')
WHERE
LENGTH(REGEXP_SUBSTR(`pAdres`,'\\*[\\/]\\d{1,4}[a-zA-Z] \\d{1,4}|\\*[\\/]\\d{1,4} \\d{1,4}|\\*[\\/]\\d{1,4} m\\d{1,4}'))
14. Wynik zapytania
Po takim oznaczeniu rekordów których nie chcemy brać pod uwagę, wyselekcjonujemy tylko te które nas interesują poniższym wyrażeniem regularnym
15. Wyrażenie regularne
[\/]\d{1,4}[a-zA-Z]|[\/]\d{1,4}
16. Zapytanie SQL
SELECT `pAdres`, REGEXP_SUBSTR(`pAdres`,'[\\/]\\d{1,4}[a-zA-Z]|[\\/]\\d{1,4}') AS 'Numer mieszkania' FROM `tabela2`
17. Rezultat zapytania SQL
18. Przeniesienie wyselekcjonowanego wzorca zapytaniem SQL do kolumny pNr_mieszkania
UWAGA - przed wykonaniem tego zapytania puste pola w kolumnie ustaw na wartość null
takim zapytaniem
UPDATE `tabela2`
SET `pNr_mieszkania`=null
WHERE
`pNr_mieszkania`=''
po ustawieniu pól na wartość NULL
czas wypełnić te pola właściwymi wartościami
UPDATE `tabela2`
SET `pNr_mieszkania`= REGEXP_SUBSTR(`pAdres`,'[\\/]\\d{1,4}[a-zA-Z]|[\\/]\\d{1,4}')
WHERE `pNr_mieszkania` IS null
dlaczego w zapytaniu jest IS null
? Dlatego, że jak nie będzie tego warunku, to wykonanie tego zapytania spowoduje, że wszystko to, co tam wcześniej wstawiliśmy usunie.
19. Rezultat dodania numerów mieszkania
do kolumny pNr_mieszkania
20. Usuwamy /
zapytaniem SQL
UPDATE `tabela2`
SET
`pNr_mieszkania`= REPLACE(`pNr_mieszkania`,'/','')
WHERE
LENGTH(REGEXP_SUBSTR(`pNr_mieszkania`,'[\\/]\\d{1,4}[a-zA-Z]|[\\/]\\d{1,4}'))
21. Rezultat
##Rozdzielanie numeracji z pkt. 4
i teraz przyszła kolej na ostatnią część przy rozdzielaniu - chodzi o zapis 20/30 5
.
W pierwszej kolejności przeniesiemy sobie wszystkie zapisy które zawierają gwiazdkę PRZED / oraz PO / -> */*
. Najpierw wykonamy zapytanie SQL aby upewnić się czy wyrażenie regularne właściwie oznacza to co chcemy.
1. Wyrażenie regularne
\d{1,4}\*\/\*\d{1,4}[a-zA-Z]|\d{1,4}\*\/\*\d{1,4}|\d{1,4}[a-zA-Z]\*\/\*\d{1,4}
2. Zapytanie SQL
SELECT `pAdres`, REGEXP_SUBSTR(`pAdres`,'\\d{1,4}\\*\\/\\*\\d{1,4}[a-zA-Z]|\\d{1,4}\\*\\/\\*\\d{1,4}|\\d{1,4}[a-zA-Z]\\*\\/\\*\\d{1,4}') AS 'Numer mieszkania **'
FROM `tabela2`
3. Rezultat zapytania (wyniki są częściowe)
4. Wyselekcjonowane rekordy przenosimy do kolumny pNr_domu
Przed wykonaniem tego polecenia polecam ustawienie we wszystkich polach które są puste - wartość NULL
UPDATE `tabela2`
SET `pNr_domu`= REGEXP_SUBSTR(`pAdres`,'\\d{1,4}\\*\\/\\*\\d{1,4}[a-zA-Z]|\\d{1,4}\\*\\/\\*\\d{1,4}|\\d{1,4}[a-zA-Z]\\*\\/\\*\\d{1,4}')
WHERE `pNr_domu` IS null
5. Rezultat wykonanego przeniesienia
6. Na koniec pozostaje pozbyć się "gwiazdek"
UPDATE `tabela2`
SET
`pNr_domu`= REPLACE(`pNr_domu`,'*','')
WHERE
LENGTH(REGEXP_SUBSTR(`pNr_domu`,'\\d{1,4}\\*\\/\\*\\d{1,4}[a-zA-Z]|\\d{1,4}\\*\\/\\*\\d{1,4}|\\d{1,4}[a-zA-Z]\\*\\/\\*\\d{1,4}'))
7. Rezultat
To już wszystko w związku z rozdzielaniem nazw ulic
, numerów domów
i numerów mieszkań
z pełnego adresu. Kolumnę która zawiera pełny adres można usunąć, w przypadku jeżeli jest już niepotrzebna. Twoja tabela jest już odpicowana i możesz spać spokojnie :D
Spójrz jeszcze na pkt. 2
który wykonałem na samym końcu, z tego względu iż jest to najtrudniejszy etap i wszystko będzie wykonane jak należy.
Nie da się ? Te, to poczymaj mi piwo... :D
Proszę o krytykę i inne uwagi, nawet te pozytywne ;) no i ciekawe co na to sam zainteresowany ? :D bo jakoś długo go nie widać ;)