Avatar billede Nicolai Nybegynder
25. september 2013 - 14:14 Der er 7 kommentarer og
1 løsning

Trimme leading og trailing spaces på alle data i en tabel

Hej,

Findes der en enkel måde jeg kan køre en query på en tabel og fjerne alle leading og trailing mellemrum (spaces) på alle data?

Jeg kører den i dag fra en VB.NET applikation, men ønsker at execute den direkte på SQL serveren som en prodecure/funktion/query:

Dette er min VB.NET kode - jeg ønsker som sagt at få den til at fungere i SQL alene uden VB.net:

frmMain.txtAction.Text = "(Import column names)"
        frmMain.Refresh()
        Dim ColumnNames As New DataTable()
        myQuery = "SELECT column_name FROM INFORMATION_SCHEMA.Columns where TABLE_NAME = 'tblXSAP_Contracts'"
        myCommand = New System.Data.SqlClient.SqlCommand(myQuery, myConnection)

        myCommand.Connection = myConnection
        myCommand.Connection.Open()

        Dim myReader As New System.Data.SqlClient.SqlDataAdapter
        myReader.SelectCommand = myCommand

        myReader.Fill(ColumnNames)

        myCommand.Connection.Close()
        myConnection.Close()

        myCommand.Connection.Close()
        myConnection.Close()

        For Each row As DataRow In ColumnNames.Rows
            Try
                frmMain.txtAction.Text = "(Clean SQL Data)"
                frmMain.Refresh()
                myQuery = "UPDATE dbo.tblXSAP_Contracts SET " & row(0) & " = LTRIM(RTRIM(" & row(0) & "))"
                myCommand = New System.Data.SqlClient.SqlCommand(myQuery, myConnection)
                myCommand.Connection = myConnection
                myCommand.Connection.Open()
                myCommand.ExecuteScalar()
                myCommand.Parameters.Clear()
                myCommand.Connection.Close()
                myConnection.Close()

            Catch ex As Exception
                myQuery99 = "INSERT INTO tblXIST_Log VALUES (@DateAndTime, @UserName, @MachineName, @System, @LogText)"
                myCommand99 = New System.Data.SqlClient.SqlCommand(myQuery99, myConnection99)
                myCommand99.Parameters.AddWithValue("@DateAndTime", Now())
                myCommand99.Parameters.AddWithValue("@UserName", System.Environment.UserName)
                myCommand99.Parameters.AddWithValue("@MachineName", System.Environment.MachineName)
                myCommand99.Parameters.AddWithValue("@System", "BASS Server")
                myCommand99.Parameters.AddWithValue("@LogText", "During update of current customers, this error occured: " & ex.Message)
                myCommand99.Connection = myConnection99
                myCommand99.Connection.Open()

                Try
                    myCommand99.ExecuteNonQuery()
                Finally

                End Try
                myCommand99.Connection.Close()
                myConnection99.Close()
            End Try
        Next row
Avatar billede anri Novice
25. september 2013 - 15:25 #1
UPDATE dbo.tblXSAP_Contracts SET [Row] = LTRIM(RTRIM([Row])
Avatar billede anri Novice
25. september 2013 - 15:36 #2
Ja ok.
sorry..  Den del havde du..  Forstod ikke spørgsmålet ved første gennemlæsning åbenbart.
Avatar billede Nicolai Nybegynder
25. september 2013 - 22:53 #3
Jeg kører i dag følgende som query, men jeg ville gerne kunne køre den i én lidt smartere query, hvor jeg ikke er tvunget til at kunne kolonne navnene i forvejen (de kan være oprettet dynamisk):

UPDATE dbo.tblXSAP_Contracts
SET "CType" = LTRIM(RTRIM("CType"))

UPDATE dbo.tblXSAP_Contracts
SET "ContractNr" = LTRIM(RTRIM("ContractNr"))

UPDATE dbo.tblXSAP_Contracts
SET "CostCenter" = LTRIM(RTRIM("CostCenter"))

UPDATE dbo.tblXSAP_Contracts
SET "Country" = LTRIM(RTRIM("Country"))

UPDATE dbo.tblXSAP_Contracts
SET "Extm" = LTRIM(RTRIM("Extm"))

UPDATE dbo.tblXSAP_Contracts
SET "Material" = LTRIM(RTRIM("Material"))

UPDATE dbo.tblXSAP_Contracts
SET "SDType" = LTRIM(RTRIM("SDType"))

UPDATE dbo.tblXSAP_Contracts
SET "SGp" = LTRIM(RTRIM("SGp"))

UPDATE dbo.tblXSAP_Contracts
SET "SerialNumber" = LTRIM(RTRIM("SerialNumber"))

UPDATE dbo.tblXSAP_Contracts
SET "SoldTo" = LTRIM(RTRIM("SoldTo"))

UPDATE dbo.tblXSAP_Contracts
SET "ValidFrom" = LTRIM(RTRIM("ValidFrom"))

UPDATE dbo.tblXSAP_Contracts
SET "ValidTo" = LTRIM(RTRIM("ValidTo"))
Avatar billede anri Novice
26. september 2013 - 08:16 #4
Tjah, det er bestemt performessigt den hurtigste løsning, men hvis fleksibilitet vægtes højere i dette projekt så fandt jeg lidt inspiration her :
http://support.microsoft.com/kb/111401/da

Det må være noget lignende det her:

Declare @ColName varchar(50);
Declare @SQL    Varchar(max);

set rowcount 0
SELECT null as mykey, column_name into #mytemp FROM INFORMATION_SCHEMA.Columns where TABLE_NAME = 'tblXSAP_Contracts'

set rowcount 1
update #mytemp set mykey = 1

while @@rowcount > 0
begin
    set rowcount 0

    select @ColName=column_name from #mytemp where mykey = 1

Set @SQL = 'UPDATE dbo.tblXSAP_Contracts SET ' + @ColName + ' = LTRIM(RTRIM(' + @ColName +'))'
Exec(@SQL)

    delete #mytemp where mykey = 1
    set rowcount 1
    update #mytemp set mykey = 1
end
set rowcount 0
Avatar billede arne_v Ekspert
27. september 2013 - 03:08 #5
En anden metode som ligner men alligevel er lidt forskellig:

DECLARE @colname VARCHAR(32)
DECLARE @coltype VARCHAR(32)
DECLARE @sql VARCHAR(255)

DECLARE c CURSOR FOR
SELECT COLUMN_NAME,DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't1'

OPEN c

FETCH NEXT FROM c INTO @colname,@coltype
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @coltype = 'varchar'
    BEGIN
        SET @sql = 'UPDATE t1 SET ' + @colname + ' = RTRIM(LTRIM(' + @colname + '))'
        PRINT @sql
        -- EXEC(@sql)
    END
    FETCH NEXT FROM c INTO @colname,@coltype
END

CLOSE c
DEALLOCATE c

GO
Avatar billede Nicolai Nybegynder
07. oktober 2013 - 15:02 #6
Jeg kan ikke få denne til at virke... Jeg har byttet t1 ud med tabelnavnet, men det siger bare "Command completed succecsfully..." efter et halvt sekund. Den laver ingen ændringer på tabellen...

Her er min ændrede kode:

DECLARE @colname VARCHAR(32)
DECLARE @coltype VARCHAR(32)
DECLARE @sql VARCHAR(255)

DECLARE c CURSOR FOR
SELECT COLUMN_NAME,DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'dbo.tblXSAP_MIF'

OPEN c

FETCH NEXT FROM c INTO @colname,@coltype
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @coltype = 'nvarchar'
    BEGIN
        SET @sql = 'UPDATE dbo.tblXSAP_MIF SET ' + @colname + ' = RTRIM(LTRIM(' + @colname + '))'
        PRINT @sql
        -- EXEC(@sql)
    END
    FETCH NEXT FROM c INTO @colname,@coltype
END

CLOSE c
DEALLOCATE c

GO
Avatar billede Nicolai Nybegynder
07. oktober 2013 - 15:05 #7
Fandt ud af at jeg skal slette "dbo." i navnet. Så virker den... Ligger du et svar.
Avatar billede arne_v Ekspert
08. oktober 2013 - 03:02 #8
svar

(du skal saa selvfoelgelig lige have enablet exec)
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Computerworld tilbyder specialiserede kurser i database-management

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester