[SQL]kolom opvullen met voorloopnullen

[Arethusa]

Al een groot aantal uurtjes loop ik tegen het volgende probleem aan.
Ik wil graag een kolom met daarin een artikelcode [AR_ARTICLECODE] van het type char opvullen met een aantal nullen zodat ik in elke kolom een veldgrootte van 7 krijg. In die velden kunnen 4 getallen staan maar ook 6 of 7. Variabel dus.

Ik heb gelezen dat bovenstaand gemakkelijk kan worden gemaakt in de presentatie laag dus in het programma of website. Het script wat ik maak moet zorgen voor een fatsoenlijk ordening het artikelcode en gaat gebruikt in een grote applicatie en dit word op veel plaatsen toegepast. Het is dus niet erg netjes om steeds de zelfde code overal neer te plakken. Vandaar het idee om dit in de SQL 2005 database te doen.

Het idee is alsvolgt:

code:

- bekijk welke velden in de kolom kleiner zijn dan 7
- loop door die velden en vul ze op een 0 totdat ze een veldgrootte van 7 hebben
- update de tabel met de juiste waarden

Alle velden moeten nu 7 groot zijn

Het opvragen van de grootte kan met len(kolomnaam). Dat is het probleem niet. Ik heb meer problemen met het kiezen van een geschikte volgorde van werken en het opvullen met het variabele aantal nullen.

Ik dacht aan een while loop of een end if loop maar met mijn huidige code wil dat niet helemaal samen werken.

SQL:

-- Declare the variable to be used.
DECLARE @MaxSize int

-- Initialize the variable.
SET @MaxSize = 7;

-- Begin query
SELECT AR_ARTICLECODE, LEN(AR_ARTICLECODE) AS LENGTH
FROM CFG_ARTICLE
WHERE AR_ARTICLECODE < @MaxSize

WHILE LEN(AR_ARTICLECODE) < @MaxSize
BEGIN
UPDATE CFG_ARTICLE.AR_ARTICLECODE
    SET AR_ARTICLECODE = 0 + AR_ARTICLECODE
END

Dit werkt helaas niet. Ik heb al vele manieren geprobeerd en gegoogled maar ergens in mijn denkpatroon gaat er wat mis. Misschien dat iemand me een idee kan geven?

[ Voor 0% gewijzigd door Arethusa op 29-09-2006 14:40 . Reden: Aanpassing script ]

[The Eagle]

Gewoon bij het invoerveld een select max van de tabel met codes doen en die met 1 ophogen bij de invoer. Zorg dat dit gebeurt bij het TOEVOEGEN van het artikel. Probleem opgelost.
En even een klein statement dat als de waarde gelijk is aan 0000000 of null, dat ie dan met 0000001 moet beginnen

[ Voor 24% gewijzigd door The Eagle op 29-09-2006 14:21 ]

[mark117]

dat zou toch kunnen met een simpele query:

update tabel set veld = right("0000000000" & Veld,7)

(of wat de syntax ook moet zijn, het gaat om de gedachte)

[Arethusa]

Ter verduidelijking. Ik maak dit script puur in de SQL Query analyzer binnen SQL Server 2005. Er komt geen ander programma bij kijken. De webapplicatie (waar dit script voor gebruikt word) staat er geheel buiten.

[P_de_B]

Ik ben er niet echt voorstander van dit hard in de db aan te passen, sowieso is je datatype CHAR voor een kolom met alleen maar integer waardes vreemd. Dan wordt er inderdaad 'verkeerd' gesorteerd.

Als het toch echt vanuit de db gepresenteerd moet worden met voorloopnullen zou ik niet de tabel updaten maar het in de SELECT query (of een view als het om meerdere select queries gaat) doen.

SQL:

SELECT LEFT('0000000',7 - LEN(AR_ARTCODE)) + AR_ARTCODE as AR_ARTCODE

Wil je wel perse de tabel updaten:

SQL:

UPDATE TABEL
SET AR_ARTCODE = LEFT('0000000',7 - LEN(AR_ARTCODE)) + AR_ARTCODE

Je hebt dan helemaal geen loop meer nodig.

[jvdmeer]

P_de_B schreef op vrijdag 29 september 2006 @ 14:28:

SQL:

LEFT('0000000',7 - LEN(AR_ARTCODE)) + AR_ARTCODE as AR_ARTCODE

SQL:

RIGHT('0000000' + AR_ARTCODE,7) as AR_ARTCODE

is makkelijker en waarschijnlijk sneller.

[P_de_B]

Je hebt gelijk idd

[Arethusa]

P_de_B , bedankt voor je hulp. Het werkt prima.

Wat betreft je opmerking over het datatype char in dit geval: Je hebt helemaal gelijk. Ik heb deze database niet ontworpen en ben van mening dat die persoon de keuze voor char naar goed beraad gemaakt heeft. Toch raar hoe je sommige zaken die wat lastig lijken toch een een enkele regel kunt oplossen.

Achteraf gezien was dit probleem niet eens een topic waard geweest maar aan de andere kant kunnen andere mensen van mijn vraagstuk en de oplossing profiteren.

Edit: zie na het posten dat jvdmeer ook een werkende oplossing gebracht heeft. Jij ook bedankt

[ Voor 9% gewijzigd door Arethusa op 29-09-2006 14:37 ]

[Verwijderd]

Oplossing denk ik, zelf even iets afmaken....

check commentaar in code, eerst zou ik geen update erin zetten, zodat je eerst kan zien wat de uitkomst is.

SQL:

DECLARE @ARTIKELCODE VARCHAR
DECLARE @NEWARTIKELCODE CHAR(7)

DECLARE Artikel_Cursor CURSOR FOR
        -- hier je selectie van je AR_ARTICLECODE
        SELECT LTRIM(RTRIM(AR_ARTICLECODE)) 
        FROM Artikelen -- <---- hier je eigen table uiteraard
        

    OPEN Artikel_Cursor
    FETCH NEXT FROM Artikel_Cursor INTO @ARTIKELCODE
    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT 'Huidige Artikelcode: ' + @ARTIKELCODE
    

        SET @NEWARTIKELCODE = RIGHT('0000000' + CAST(@ARTIKELCODE AS VARCHAR), 7)

        PRINT 'Nieuw Artikelcode: ' + @NEWARTIKELCODE

        -- Hier je update statement
        -- iets van: UPDATE Artikelen SET AR_ARTICLECODE = @NEWARTIKELCODE  WHERE AR_ARTICLECODE = @ARTIKELCODE
        FETCH NEXT FROM Artikel_Cursor INTO @ARTIKELCODE
    END
    CLOSE Artikel_Cursor
DEALLOCATE Artikel_Cursor
GO

----------------------
begin editmode
----------------------
hmm waarom pakte ik dit zo wazig aan... het is ind simpeler te doen zie ik nu
----------------------
eind editmode
----------------------

[ Voor 9% gewijzigd door Verwijderd op 29-09-2006 14:47 ]

[P_de_B]

Verwijderd schreef op vrijdag 29 september 2006 @ 14:44:
Oplossing denk ik, zelf even iets afmaken....

check commentaar in code, eerst zou ik geen update erin zetten, zodat je eerst kan zien wat de uitkomst is.

SQL:

knip

Het zal best werken, maar zie je hoe de oplossing van mij werkt? SQL is een setgebaseerde taal. Dat wil zeggen dat je snel operaties op een hele bak met gegevens kunt doen, in plaats van er (procedureel of met een loop) doorheen te lopen. Een cursor is bijna nooit nodig.

[Verwijderd]

P_de_B schreef op vrijdag 29 september 2006 @ 14:48:
[...]

Het zal best werken, maar zie je hoe de oplossing van mij werkt? SQL is een setgebaseerde taal. Dat wil zeggen dat je snel operaties op een hele bak met gegevens kunt doen, in plaats van er (procedureel of met een loop) doorheen te lopen. Een cursor is bijna nooit nodig.

zie mijn edit
Ach hij kan het bekijken als een cursor voorbeeld hehe.

[jvdmeer]

Verwijderd schreef op vrijdag 29 september 2006 @ 14:49:
Ach hij kan het bekijken als een cursor voorbeeld hehe.

Alsjeblieft, gebruik geen cursors... zoek maar even rond op internet waarom.

Gebruik dan een tijdelijke tabel of een zgn. tabel-variabele.

En moet je inderdaad door alle records lopen, gebruik dan eventueel:

SQL:

-- Zet teller op begin tabel
SET @CurRecord=0
-- Loop door lus zolang er nog records zijn

WHILE (SELECT count(*) FROM Tabel WHERE ID>@CurRecord)>0
BEGIN
    -- Haal eerstvolgende record op
    SELECT TOP 1 @CurRecord=ID FROM Tabel WHERE ID>@CurRecord ORDER BY ID

    -- Verwerk record
    UPDATE Tabel SET Veld=Waarde WHERE ID=@CurRecord
END

[ Voor 9% gewijzigd door jvdmeer op 29-09-2006 15:55 ]