[SQL] parsing van strings

[justice strike]

Ik ben een tijdje op zoek naar de mogelijkheid om strings te parsen aan de hand van een delimiting character (in dit geval een tab). Nu weet ik dat het waarschijnlijk heel makkelijk kan met pl/sql of t/sql. Maar ik wil het nu eigenlij in plain sql hebben zodat het portable is.

Iemand enig idee? ik heb namelijk wel even zitten kijken, maar vrijwel alles verwijst naar een programmer taal

[Cartman!]

Misschien handig om een voorbeeld te vermelden? Het komt met je beknopte uitleg over alsof er iemand heeft gefaald tijdens t maken van een correct datamodel namelijk

[justice strike]

Cartman! schreef op maandag 26 januari 2009 @ 11:03:
Misschien handig om een voorbeeld te vermelden? Het komt met je beknopte uitleg over alsof er iemand heeft gefaald tijdens t maken van een correct datamodel namelijk

text file word geimporteerd. Het bestand is tab delimitated en wordt eerst in een enkel kolom tabel gezet. daarna geparsed.

Text file wordt met een standaard tool ingelezen dus vandaar dat dat niet meteen uitgesplitst word.

Het is niet mogelijk om het anders in te lezen.

[ Voor 4% gewijzigd door justice strike op 26-01-2009 11:11 ]

[Motrax]

Niet gefaald. Ik ben er in de praktijk ook tegen aan gelopen. Bronsysteem levert data aan in een free text veld waar door kopieer-plakwerk ook non-unicode waarden in staan, toevalig in hex is het een regel en zelfs bestandseinde. Als je dat weer uit je eigen database uitleest en in een flat file plaatst heb je een grote zooi met het overnieuw inlezen van de flat file.

In theorie moet je het bij de bron oplossen. In de praktijk ga je filteren op je eigen database.

Er bestaan wel functies om in je varchar veld te zoeken op hex, maar geen idee welke dat ook al weer was. Of was dat de externe tool die ik gebruikte...?

Edit:
Ik zie dat je een groter probleem hebt, je hebt een bron flat file met verkeerde hex waarden al. Het punt is dat je dan tijdens het inlezen van het bestand moet filteren op de onjuiste hex waarden, maar de juiste row delimiters moet laten staan. Aangezien het inlezen van een bestand vaak standaardprogrammatuur is en SQL pas op een later moment komt kijken is dat een probleem.

Misschien dat je met SQL Loader voor Oracle al in het data definitie bestand SQL op veld niveau kan gebruiken, maar ik betwijfel of dat al kan en ik betwijfel of dat goed wordt uitgevoerd.

[ Voor 31% gewijzigd door Motrax op 26-01-2009 11:13 ]

[justice strike]

Motrax schreef op maandag 26 januari 2009 @ 11:11:
Niet gefaald. Ik ben er in de praktijk ook tegen aan gelopen. Bronsysteem levert data aan in een free text veld waar door kopieer-plakwerk ook non-unicode waarden in staan, toevalig in hex is het een regel en zelfs bestandseinde. Als je dat weer uit je eigen database uitleest en in een flat file plaatst heb je een grote zooi met het overnieuw inlezen van de flat file.

In theorie moet je het bij de bron oplossen. In de praktijk ga je filteren op je eigen database.

Er bestaan wel functies om in je varchar veld te zoeken op hex, maar geen idee welke dat ook al weer was. Of was dat de externe tool die ik gebruikte...?

Eigenlijk wil ik het netjes in standaard sql oplossen aangezien het wel mogelijk moet zijn (lees net een gepubliceerd wetenschappelijk artikel erover) maar dat ging over woorden zoeken in een string, en was nou niet echt heel overzichtelijk code en/of datgene wat ik zocht (character delimited)

Voordat iemand een fout maakt. De rows zijn goed. De textfile is correct ingelezen en elke regel heeft een eigen row. Nu wil ik de data in die regel nog parsen en in kolommen opdelen.

[ Voor 8% gewijzigd door justice strike op 26-01-2009 11:16 ]

[Janoz]

justice strike schreef op maandag 26 januari 2009 @ 11:15:
[...]

Eigenlijk wil ik het netjes in standaard sql oplossen aangezien het wel mogelijk moet zijn

Als je het netjes standaard op wilt lossen zul je er voor moeten zorgen dat de tussenstap er tussenuit gehaald wordt. Zorg gewoon dat er tijdens het inlezen al geknipt wordt op het scheidingsteken. Als 'netheid' je motivator is dan vind ik dat je je probleem op de verkeerde plek aan het oplossen bent.

[Motrax]

Wait... what?

Je hebt een tab delimited file en je wil het daarna pas splitsen? Dan maak je iets simpels een stuk ingewikkelder

Enige wat ik kan verzinnen 123 is om substr() te gebruiken, maar dan moet je wel vaste posities hebben. En heb je de tab er nog niet uitgefilterd...

Kan je echt niet het laadproces aanpassen?

[Zoijar]

Dit? http://bytes.com/groups/m...6-how-extract-part-string

(zoiets dus:

SELECT SUBSTRING([Ref], CHARINDEX('(oc:', [Ref]) + 4, LEN([Ref]) -
(CHARINDEX('(oc:', [Ref]) + 4))
FROM TABLENAME
WHERE REF LIKE '%(oc:%'

en dan die inserten.. wel ingewikkeld. Waarom niet gewoon een import? Je kan een text file gewoon als data source opgeven en dan rechtstreeks kopieren)

[ Voor 70% gewijzigd door Zoijar op 26-01-2009 11:33 ]

[Hydra]

Vind de oplossingsrichting een beetje 'creepy'. IMHO is de 'lees'applicatie verantwoordelijk voor het opschonen / reformatten van de data. Als je dat in SQL doet, en toevallig een keer werkt met een implementatie die niet helemaal doet wat je veracht, heb je je database vol zitten met rotzooi.

[justice strike]

in het laad process kan het jammer genoeg niet.

dit had ik gevonden voor word parsing... maar je wordt er niet gelukkig van!

code:

INSERT INTO DocTerm
(DocID, TermNumber, Term)
SELECT f.DocID, f.TermNumber
, SUBSTRING(g.TextBlock FROM f.TermStartPos FOR
(1 + f.TermEndPos - f.TermStartPos)) AS Term
FROM
(SELECT d.DocID, d.TermNumber
, MIN(d.IndexPos) AS TermStartPos
, MAX(d.IndexPos) AS TermEndPos
FROM
(SELECT a.DocID
, SUBSTRING(a.TextBlock FROM b.IndexPos
FOR 1) AS ParsedCharacter
, b.IndexPos
, SUM(CASE WHEN c.ParsingCharacter IS NULL
THEN 1 END) OVER (PARTITION BY
a.DocID ORDER BY a.DocID, b.IndexPos
ROWS UNBOUNDED PRECEDING)
AS TermNumber
FROM DocTemp a
CROSS JOIN IndexPos b
LEFT OUTER JOIN ParsingCharacter c ON
ParsedCharacter = c.ParsingCharacter
QUALIFY c.ParsingCharacter IS NOT NULL) d
GROUP BY d.DocID, d.TermNumber) f
INNER JOIN DocTemp g ON f.DocID = g.DocID

goed, ik begrijp hieruit dus dat ik beter maar aan pl/sql kan gaan denken.

[ Voor 4% gewijzigd door justice strike op 26-01-2009 11:41 ]

[SchizoDuckie]

justice strike schreef op maandag 26 januari 2009 @ 11:40:
in het laad process kan het jammer genoeg niet.

dit had ik gevonden voor word parsing... maar je wordt er niet gelukkig van!

code:

*snap*

goed, ik begrijp hieruit dus dat ik beter maar aan pl/sql kan gaan denken.

Nee, je begrijpt ons verkeerd. Je wil geen tab delimited meuk aan je database gaan voeren zodat die dat vervolgens nog een keer gaat parsen.

Zorg dat de code-tier die die database queries uitvoert gewoon het hele spulletje opsplitst en dan losse inserts uitvoert. Dan ben je veel flexibeler in het parsen ook.

Dit soort dingen is geen taak voor SQL.

[MBV]

Ik begrijp dat je probleem is dat een andere applicatie (die je niet kan/mag aanpassen) zooi in jouw database gooit, en dat jij dat gestructureerd wilt maken? Vrij simpel: pak een programmeertaal, schrijf een script wat die rommel-tabel uitleest, parse het, en schrijf het weg in jouw opgeruimde tabel.

[Motrax]

Duidelijk. Ontwerptechnisch is het niet zo tof. Maar de theorie is niet altijd de praktijk.

En nu een oplossing voor deze vraag, aangezien het wel vaker voorkomt Ik heb er in ieder geval interesse in.

Ik zie een paar punten die opgelost moeten worden:
1) Filteren op hex naar tab
2) Als je het gevonden hebt uitsplitsen naar nieuwe velden

Punt 1) is misschien nog op te lossen, maar Punt 2) zou ik echt niet weten.

Is het mogelijk om de tabel bruut te dumpen naar een flat file en dan opnieuw correct (!) in te lezen?

[Voutloos]

Na import deze lelijke tabel lezen en dmv je favo programmeertaal de juiste tabel vullen kan natuurlijk ook altijd.

[justice strike]

Motrax schreef op maandag 26 januari 2009 @ 12:27:
Duidelijk. Ontwerptechnisch is het niet zo tof. Maar de theorie is niet altijd de praktijk.

En nu een oplossing voor deze vraag, aangezien het wel vaker voorkomt Ik heb er in ieder geval interesse in.

Ik zie een paar punten die opgelost moeten worden:
1) Filteren op hex naar tab
2) Als je het gevonden hebt uitsplitsen naar nieuwe velden

Punt 1) is misschien nog op te lossen, maar Punt 2) zou ik echt niet weten.

Is het mogelijk om de tabel bruut te dumpen naar een flat file en dan opnieuw correct (!) in te lezen?

Het was ten eerste een technische casus voor mij. Ik heb nu wel enigszins alles geordend in rijen (alhoewel de getall;en totaal niet goed in de database komen) dit heb ik overigens niet via sql gedaan.

Goed, er is een spreekwoord voor deze casus overigens. "Implementeren is het omzeilen van bugs"

[Hydra]

justice strike schreef op maandag 26 januari 2009 @ 12:45:
Het was ten eerste een technische casus voor mij.

Als er geen reden (legacy softare zonder source bijv.) is om niet voor de 'juiste' oplossing te gaan, is er m.i. geen reden om dit in SQL zelf af te handelen. Wat is nu de reden dat je dit in 'plain' SQL wilt doen?

[justice strike]

Hydra schreef op maandag 26 januari 2009 @ 12:52:
[...]


Als er geen reden (legacy softare zonder source bijv.) is om niet voor de 'juiste' oplossing te gaan, is er m.i. geen reden om dit in SQL zelf af te handelen. Wat is nu de reden dat je dit in 'plain' SQL wilt doen?

mwa. juist juist. Het is altijd wel handig om dit soort dingen te kunnen in een database. het is heel makkelijk om alles uit een database te halen en te gaan parsen, maar het lijkt mij mooier om dat dan binnen een database te kunnen (als het al verkeerd erin staat)

[ Voor 17% gewijzigd door justice strike op 26-01-2009 13:13 ]

[MBV]

Databases zijn hier totaal niet voor gemaakt, dus dat het handiger is om het binnen de database te doen lijkt mij een foute aanname.
Maar PL/SQL is ook een imperatieve programmeertaal, die je voor dit soort dingen kan gebruiken. Het kan netwerkverkeer schelen om het op de databaseserver uit te voeren, dus dat kan je met PL/SQL doen. Ik zou wel de tabellen gescheiden houden.

[justice strike]

MBV schreef op maandag 26 januari 2009 @ 13:34:
Databases zijn hier totaal niet voor gemaakt, dus dat het handiger is om het binnen de database te doen lijkt mij een foute aanname.
Maar PL/SQL is ook een imperatieve programmeertaal, die je voor dit soort dingen kan gebruiken. Het kan netwerkverkeer schelen om het op de databaseserver uit te voeren, dus dat kan je met PL/SQL doen. Ik zou wel de tabellen gescheiden houden.

het is me wel duidelijk dat het niet makkelijker is. Maar het is soms wel handig.

[Zoijar]

Je kan ook externe code aanroepen vanuit een stored procedure. Dat kan je dan in een trigger zetten als er nieuwe legacy data wordt geinsert door dat process waar je geen source code van hebt.

[Hydra]

justice strike schreef op maandag 26 januari 2009 @ 13:08:
mwa. juist juist. Het is altijd wel handig om dit soort dingen te kunnen in een database.

Waarom is dat handig?

Zoijar schreef op maandag 26 januari 2009 @ 13:46:
Je kan ook externe code aanroepen vanuit een stored procedure. Dat kan je dan in een trigger zetten als er nieuwe legacy data wordt geinsert door dat process waar je geen source code van hebt.

Dat is net zo min DB-onafhankelijk.

[ Voor 42% gewijzigd door Hydra op 26-01-2009 14:01 ]

[justice strike]

Hydra schreef op maandag 26 januari 2009 @ 14:00:
[...]


Waarom is dat handig?

omdat SQL, db en applicatie onafhankelijk is.(hoort te zijn)

[MBV]

Huh? Je maakt hier alles juist heel erg afhankelijk van elkaar. Door een idioot input-formaat in je database te hebben, en dat in je database te willen aanpassen, wordt je afhankelijk van het input-formaat en je DBMS. Je moet in mijn ogen juist ervoor zorgen dat de input op een goede manier in de database beland, en rechtstreeks een CSV-bestand in de database plempen hoort daar niet bij.

[justice strike]

MBV schreef op maandag 26 januari 2009 @ 14:45:
Huh? Je maakt hier alles juist heel erg afhankelijk van elkaar. Door een idioot input-formaat in je database te hebben, en dat in je database te willen aanpassen, wordt je afhankelijk van het input-formaat en je DBMS. Je moet in mijn ogen juist ervoor zorgen dat de input op een goede manier in de database beland, en rechtstreeks een CSV-bestand in de database plempen hoort daar niet bij.

mwa ik heb het over de parsing ansich, niet zozeer dat de input fout is. Zoals hierboven gezegd wordt, het is handig om het in de praktijk te kunnen ondanks dat het in theorie slecht is.

[ Voor 10% gewijzigd door justice strike op 26-01-2009 14:58 ]

[Gabberhead]

Op zich kan het wel simpel in PL/SQL, alleen zul je dan gebruik moeten maken van substr en instr (en dan ben je dus niet meer gegarandeerd van eenzelfde werking in een andere database).

Stel ik heb een tabel met kolom a en daarin de string 'abcdef;ghijk;lmnop;qrstu;'
Als ik daar nu de verschillende stukken uit wil halen die worden gescheiden door ;, krijg je het volgende statement:

code:

select substr(a,0,instr(a,';',1,1)-1) 
     , substr(a,instr(a,';',1,1)+1,instr(a,';',1,1)-2)
     , substr(a,instr(a,';',1,2)+1,instr(a,';',1,1)-2)
     , substr(a,instr(a,';',1,3)+1,instr(a,';',1,1)-2)
  from tabel

Maar dan moet je dus wel vantevoren weten hoeveel waardes je per regel terug kunt verwachten.

[Zoijar]

Hydra schreef op maandag 26 januari 2009 @ 14:00:
Dat is net zo min DB-onafhankelijk.

True...

Ik snap het sowieso niet. Of je hebt bestaande data in dat formaat al in een database zitten; dan kan je het gewoon een keer in een file opslaan en het importen. Of je hebt een legacy systeem met bestaande code (zonder source) die het continu zo opslaat in de database; in dat geval weet je toch wat voor database je gebruikt?

Enige manier waarop het "fout" kan gaan is als iemand anders dat legacy systeem gaat gebruiken door gewoon de executables te kopieren en er dan een ander type database aanhangt. Dat is sowieso een riskante onderneming. Bovendien moeten ze dan alsnog zelf de SQL update query in hun nieuwe database integreren. Dan kan je ook gewoon eisen dat het ten minste een T of PL SQL database moet zijn en dan twee queries ter beschikking stellen.

De gevraagde oplossing is zuiver theoretisch in de praktijk volgens mij nutteloos, hoewel er juist gesteld wordt dat het in de praktijk nuttig is.

[Motrax]

Nuttig? Zeker wel. Niet met een normale transactionele database in een normale omgeving.

Maar met een dataschoning, datacleaning en/of ETL staging database in combinatie met inflexibele bronsystemen, legacy troep en een sausje bureaucratie zal je versteld staan wat er in de praktijk te vinden is.

[justmental]

Zo te zien werk je met oracle.
Ik zou een external table definieren met je bestandsformaat.
De verwerking kun je dan met een willekeurige programmeertaal, bijvoorbeeld java als je platformonafhankelijk wilt zijn.
Andere databases hebben waarschijnlijk wel vergelijkbare technologieen als die external table en anders is maar minimale specifieke code nodig.

[Hydra]

justice strike schreef op maandag 26 januari 2009 @ 14:02:
omdat SQL, db en applicatie onafhankelijk is.(hoort te zijn)

Ja, maar de dingen die jij wil zijn dat dus absoluut niet. SQL is, zoals de naam zegt, een query taal. Het is niet bedoeld om import op te schonen ofzo. Je zdgt dat het puur theoretisch is, nou prima, je weet dat je in SQL wat methoden hebt zaakjes te reformatten enzo, maar laat het ajb daarbij. Ik heb al te veel Oracle DBA's gezien die vinden dat business logic in het RDBMS thuishoort

[justice strike]

Hydra schreef op maandag 26 januari 2009 @ 16:30:
[...]


Ja, maar de dingen die jij wil zijn dat dus absoluut niet. SQL is, zoals de naam zegt, een query taal. Het is niet bedoeld om import op te schonen ofzo. Je zdgt dat het puur theoretisch is, nou prima, je weet dat je in SQL wat methoden hebt zaakjes te reformatten enzo, maar laat het ajb daarbij. Ik heb al te veel Oracle DBA's gezien die vinden dat business logic in het RDBMS thuishoort

je hebt je punt gemaakt, nu nog het antwoord Zo'n casus geeft mij wat meer inzicht over de mogelijkheden en hoe deze toe te passen. Ik weet al hoe je relationele algebra, tupelcalculus domeincalculus etc.. om moet schrijven naar SQL. Maar zoiets schijnbaar makkelijks als parsen is mij nog niet erg duidelijk.

[ Voor 19% gewijzigd door justice strike op 26-01-2009 21:09 ]