[SQL] String vervangen

[Josh]

De topictitel klinkt simpel, maar eigenlijk kom ik er totaal niet uit! Het vervangen van een string in een field van een tabel doe ik normaliter natuurlijk met:

code:

update *tabel* set *field_name* =  replace (*field_name*, 'zoek_dit', 'vervangen_met_dit')

Maar nu wil het zo zijn dat ik een stukje HTML moet vervangen:

code:

a> <a href

Dus heb ik dit geprobeerd:

code:

update cms_props set content =  replace (content, 'a> <a href', 'a> deze <a href')

Maar dan geeft mySQL aan dat er geen rijen veranderd zijn terwijl ik zeker weet dat deze code voor komt in een veld.

Hoe komt het nou dat SQL dit niet snapt? Voor zover ik weet zijn "<" en ">" geen characters die ik moet escapen? Bovendien heb ik dat al geprobeerd, maar zonder resultaat. Wat doe ik verkeerd hier? Ik ben al meer dan een uur hiermee bezig!

[ Voor 18% gewijzigd door Josh op 10-01-2010 03:32 ]

[NMe]

Ik heb geen idee wat je verkeerd doet, je geeft namelijk helemaal niet aan wat er fout gaat en je query is dermate geabstraheerd dat ik daar ook geen fouten in zou kunnen zien. Geef eens wat échte info in plaats van het posten van een willekeurige pseudoquery met de melding dat 'ie het niet doet. Anders edit je je post even.

Ik durf bijna te wedden dat de spatie tussen de twee tags geen echte spatie is maar een ander stukje whitespace.

Overigens is het vervangende stukje HTML invalid.

[ Voor 25% gewijzigd door NMe op 10-01-2010 03:21 ]

[Josh]

NMe schreef op zondag 10 januari 2010 @ 03:19:
Ik heb geen idee wat je verkeerd doet, je geeft namelijk helemaal niet aan wat er fout gaat en je query is dermate geabstraheerd dat ik daar ook geen fouten in zou kunnen zien. Geef eens wat échte info in plaats van het posten van een willekeurige pseudoquery met de melding dat 'ie het niet doet. Anders edit je je post even.

Ik durf bijna te wedden dat de spatie tussen de twee tags geen echte spatie is maar een ander stukje whitespace.

Overigens is het vervangende stukje HTML invalid.

Hehe, sorry voor de edit, ik bedacht me namelijk precies wat jij dacht dat er iets mistte in de omschrijving.

Dat vermoeden heb ik ook. Ik kwam er namelijk ook achter dat er verschillende witlijnen in het veld zitten. De vorige webmaster heeft deze velden gevuld met een script dat automatisch tekst maakte en het is erg rommelig.

Je let wel goed op trouwens, op dit tijdstip nog. HTML was inderdaad invalid, maar was een voorbeeld wat ik getypt had

Er is iets meer aan de hand denk ik. Wanneer ik deze query gebruik (even een ander bijvoorbeeld):

code:

SELECT *
FROM `database`.`table`
WHERE `content` LIKE '%>VIP chauffeur</a>.%'
LIMIT 0 , 30

Dan krijg ik netjes de rijen terug die die HTML code bevatten.

Maar wil ik vervolgens de boel vervangen:

code:

update table set content =  replace (content, '%>VIP chauffeur</a>.%', '%>VIP chauffeur</a>%')

Dan krijg ik terug dat 0 rijen veranderd zijn en ook zonder de %-tekens gebeurt er niets.

[ Voor 77% gewijzigd door Josh op 10-01-2010 12:36 ]

[Grijze Vos]

Volgens mij horen die % tekens niet in je replace statement.

[NMe]

Wildcards horen daar inderdaad niet.

Heb je de string die je wil gaan kopiëren overgetikt of gecopy-pasted?

[dusty]

Zoals grijze vos al aangeeeft: % in je sql statement zijn wildcards, die in de replace zijn geen wildcards...

[Josh]

Jullie hebben allemaal gelijk! Maar zoals ik eronder ook beschrijf, zonder de % wildcards doet de string het ook niet. Zo dus:

code:

update table set content =  replace (content, '>VIP chauffeur</a>.', '>VIP chauffeur</a>')

En dit is inderdaad gecopy/paste (PHPMyAdmin).

[Woy]

Heeft het niet iets met upper/lower casing te maken?

[Remus]

Wat toont:
[code=sql]
SELECT content, replace(content, '>VIP chauffeur</a>.', '>VIP chauffeur</a>') FROM table WHERE content LIKE '%>VIP chauffeur</a>%'
[code]

Doe anders een hexdump van de betreffende string en controleer of er geen unprintable characters of iets anders 'raars' tussen zit.

[Josh]

Woy schreef op zondag 10 januari 2010 @ 12:53:
Heeft het niet iets met upper/lower casing te maken?

Bingo! Het lijkt erop dat dat het inderdaad was. Deze query deed het wel:

code:

update table set content =  replace (content, 'chauffeur</a>.', 'chauffeur</a>')
Affected 4 rows

Hoe kan het nou dat SQL die upper case niet leuk vind in de replace functie? Ik lees hier niets over terug in de reference manual.

[Woy]

Het heeft dan waarschijnlijk gewoon te maken met de case sensitivity. LIKE heeft daar afhankelijk van de database/instellingen soms ander gedrag. Het lijkt me dat LIKE hier niet case sensitive is en de replace wel.

[Thomasje]

Wat is je collation op die kolom?

[Killemov]

Hmmm ... dit is of heel slim of heel ruk van mysql. Volgens de sql-standaard moeten, als je geen where clausule gebruikt, alle rows aangepast worden, of het nu klopt wat er in de search/replace staat of niet.

[Gomez12]

Killemov schreef op zondag 10 januari 2010 @ 15:03:
Hmmm ... dit is of heel slim of heel ruk van mysql. Volgens de sql-standaard moeten, als je geen where clausule gebruikt, alle rows aangepast worden, of het nu klopt wat er in de search/replace staat of niet.

Nope, alle rows moeten langsgelopen worden ( wat ook gebeurt ).

Mysql rapporteert alleen de gewijzigde en dat zijn er nul.

[Josh]

Inmiddels meer logische verklaring gevonden:

Er zat in de string een "new line" als \r\n. Door deze eerst te verwijderen (of door hem mee te nemen in de query) werkt het nu wel.

[ Voor 58% gewijzigd door Josh op 10-01-2010 16:22 ]

[Verwijderd]

Ik wil wel even aangeven, ondanks dat het niet gevraagd wordt door de TS, dat het vervangen van HTML in een database spuuglelijk is. Ik weet niet precies waarom je het wilt doen, maar als je al de route neemt om HTML in een database te knallen (kan best zijn dat je daar niks aan kunt doen doordat je bepaalde dingen door je eindgebruikers met een CMS laat regelen) dan kun je toch beter die HTML ophalen, bewerken in bijvoorbeeld PHP en dan weer opslaan in je database. PHP is veel krachtiger en preciezer voor dit soort zaken doordat je bijvoorbeeld regex kunt gebruiken, debugmogelijkheden hebt (zelfs een simpele var_dump had je al veel geleerd in dit geval) en de mogelijkheid hebt om door een gesplitte string heen te loopen.

[NMe]

Dat zijn amper argumenten aangezien sommige smaken van SQL ook regexes ondersteunen en je natuurlijk elke update eerst als select kan schrijven danwel transactions kan gebruiken om de kwaliteit te bewaken. Natuurlijk is PHP er meer voor geschikt maar als je beter bent in SQL en het toch maar een eenmalige actie is zie ik geen reden om het niet te doen.

[cariolive23]

Perl is nog beter in regexen en in PostgreSQL kun je ook Perl gebruiken: PL/Perl. Dat geeft je in principe voldoende regex-geschut om je data te bewerken.

Gebruik de optie waar je de meeste kennis en ervaring mee hebt, dat zorgt voor de minste problemen en uitdagingen.

[BikkelZ]

Dat klopt op zich wel, maar normaliter zou je het bewerken van HTML zeker niet in de database moeten laten plaatsvinden omdat dat te maken heeft met presentatie en niet met gegevens. Zelfde reden ook waarom ik eigenlijk helemaal geen HTML in een database zou willen tenzij ik er niet onderuit kom. Ik heb al hele gekke dingen voorbij zien komen, zoals tabellen die in een stored procedure werden opgebouwd

[NMe]

BikkelZ schreef op woensdag 20 januari 2010 @ 12:40:
Dat klopt op zich wel, maar normaliter zou je het bewerken van HTML zeker niet in de database moeten laten plaatsvinden omdat dat te maken heeft met presentatie en niet met gegevens. Zelfde reden ook waarom ik eigenlijk helemaal geen HTML in een database zou willen tenzij ik er niet onderuit kom. Ik heb al hele gekke dingen voorbij zien komen, zoals tabellen die in een stored procedure werden opgebouwd

Maar dat is hier helemaal niet wat er gebeurt. De HTML staat nu eenmaal al in de database, de topicstarter wil nu alleen alles in één keer even updaten om iets te fixen. Daar ontkom je af en toe niet aan. In welke omgeving je deze omzetting vervolgens doet is totaal niet relevant, noch het argument "presentatie moet je niet regelen in je database" aangezien dat gewoon geen keuze is hier.

[BikkelZ]

Klopt, maar dan wordt het een theorie versus praktijk verhaal. In theorie had hij dit niet zo moeten doen, in de praktijk zit er nou eenmaal HTML in de database. In theorie wil hij het liever in SQL fixen, maar in de praktijk was hij drie kwartier minder lang bezig als hij het in PHP had gedaan

[NMe]

In de praktijk zou jij drie kwartier minder lang bezig zijn als je het in PHP had gedaan. Misschien is de topicstarter wel helemaal niet bekend met PHP.