August 30, 2005 at 6:07 pm
hi,
I have a table which have 108 coloumns. The table has around 50 rows i.e. it contains 50 records. I want to TRIM(LTRIM and RTRIM) all the fields of the table i.e. remove the trailing and leading whitespaces from the fields using an UPDATE statement in T-SQL.
Is there are T-SQL command by which I perform the above function just by giving one command(like an UPDATE command). I don't want to specify each and every coloumn names.
August 30, 2005 at 6:49 pm
maybe there's a better way of doing this but I can only think of looping through all the column names and updating each using a cursor...something like this...
CREATE PROCEDURE UpdateALLColumns AS DECLARE @ColName VarChar(75) DECLARE Scroll_ColumnNames CURSOR SCROLL FOR SELECT sc.name FROM sysobjects so INNER JOIN syscolumns sc ON so.id = sc.id AND so.name = 'myTable' OPEN Scroll_CellValues FETCH NEXT FROM Scroll_ColumnNames INTO @ColName WHILE @@FETCH_STATUS = 0 BEGIN UPDATE myTable SET @ColName = REPLACE(@colName, ' ', '') FETCH NEXT FROM Scroll_ColumnNames INTO @ColName END CLOSE Scroll_ColumnNames DEALLOCATE Scroll_ColumnNames
**ASCII stupid question, get a stupid ANSI !!!**
August 31, 2005 at 7:30 am
It can be done in a DTS activeX script :
This code would need to be adapted but you'll get the idea. This loops in all the user tables, gets the list of nullable columns and count the number of non null rows in each column. The great thing about this code is that there's only 1 table scan and not 1 per column.
Function Main()
Dim MyRsCols
Dim MyRsCount
Dim MyCn
Dim OColumns
Dim OTable
Dim oField
Dim sTable
Dim iCount
SET MyCn = CreateObject ("ADODB.Connection")
SET MyRsCols = CreateObject ("ADODB.RecordSet")
SET MyRsCount = CreateObject ("ADODB.RecordSet")
MyCn.Open ("Provider=sqloledb;Data Source=SERVEUR4;Initial Catalog=Ideal;Integrated Security=SSPI;")
MyRsCols.Open "Select O.Name, NULLIF(dbo.ListTableColumns_NotNulls(O.id), '') as NullColumns from dbo.SysObjects O WHERE O.XType = 'U' and LEN(dbo.ListTableColumns_NotNulls(O.id)) < 7000 order by O.Name", MyCn, 1, 3
if not MyRsCols.EOF THEN
SET OTable = MyRsCols.Fields ("Name")
SET OColumns = MyRsCols.Fields ("NullColumns")
WHILE NOT MyRsCols.EOF
if not ISNULL(OColumns.value) THEN
MyRsCount.Open "Select '[" & OTable.Value & "]', " & OColumns.Value & " FROM dbo.[" & OTable.value & "]", MyCn, 1, 3
sTable = ""
iCount = 0
For each oField in MyRsCount.Fields
if iCount = 0 then
sTable = MyRsCount.Fields(0).Value
else
MyCn.Execute "EXEC dbo.TablesColumnsNulls_Insert '" & sTable & "', '" & OField.Name & "', " & oField.Value
end if
iCount = iCount + 1
next
MyRsCount.Close
END IF
MyRsCols.MoveNext
WEND
END IF
MyRsCols.Close
MyCn.Close
SET MyRsCount = NOTHING
SET MyRsCols = NOTHING
SET MyCn = NOTHING
Main = DTSTaskExecResult_Success
End Function
the server side code :
IF Object_id('ListTableColumns_NotNulls') > 0
DROP FUNCTION dbo.ListTableColumns_NotNulls
GO
IF Object_id('TablesColumnsNulls') > 0
DROP TABLE dbo.TablesColumnsNulls
GO
IF Object_id('TablesColumnsNulls_Insert') > 0
DROP PROCEDURE dbo.TablesColumnsNulls_Insert
GO
CREATE FUNCTION dbo.ListTableColumns_NotNulls (@TableID as int)
RETURNS varchar(8000)
AS
BEGIN
Declare @Items as varchar(8000)
SET @Items = ''
SELECT
@Items = @Items + 'COUNT([' + C.Name + ']) [' + C.Name + '],'
FROM dbo.SysColumns C
WHERE C.id = @TableID
AND OBJECTPROPERTY(@TableID, 'IsTable') = 1
AND C.IsNullAble = 1
AND XType NOT IN (34,35,36,99)
ORDER BY C.Name
SET @Items = LEFT(@Items, ABS(LEN(@Items) - 1))
RETURN @Items
END
GO
CREATE TABLE dbo.TablesColumnsNulls
(
TableName sysname not null,
DateLog datetime not null default (GetDate()),
ColumnName sysname not null,
QtyNonNulls int not null,
primary key clustered (DateLog, TableName, ColumnName)
)
GO
CREATE PROCEDURE dbo.TablesColumnsNulls_Insert @TableName as sysname, @ColumnName as sysname, @QtyNonNulls as int
AS
SET NOCOUNT ON
Insert into dbo.TablesColumnsNulls (TableName, ColumnName, QtyNonNulls) values (@TableName, @ColumnName, @QtyNonNulls)
SET NOCOUNT OFF
GO
August 31, 2005 at 11:23 am
Here's the SP I wrote to do this, dynamically reading the system tables to get the columns and build the SQL statement to trim all columns. As always, no guarantee and use at your own risk...
CREATE PROCEDURE dbo.usp_Trim_All_Columns
(
@Stable varchar(255)
)
AS
BEGIN
DECLARE @nRC int
DECLARE @sSQL varchar(8000)
DECLARE @sSQLCursor nvarchar(2000)
DECLARE @sColName varchar(500)
DECLARE @sTable_DB varchar(100)
DECLARE @sTable_Owner varchar(100)
DECLARE @sTable_Table varchar(100)
DECLARE @nFirstDot int
DECLARE @nSecondDot int
SET @nRC = 0
SET NOCOUNT ON
-- Don't know whether this will contain DB/Owner/Table, Owner/Table or just Table, so it may need to be split to get Table name
SET @nFirstdot = CHARINDEX('.',@sTable)
SET @nSeconddot = CHARINDEX('.', @Stable, @nFirstdot + 1)
SET @sTable_DB = ''
SET @sTable_Owner = ''
SET @sTable_Table = ''
-- contains db/owner/table
IF @nFirstDot <> 0 and @nSecondDot <> 0
BEGIN
SET @sTable_DB = SUBSTRING(@sTable, 1, @nFirstDot - 1)
SET @sTable_Owner = SUBSTRING(@sTable, @nFirstDot + 1, (@nSecondDot - @nFirstDot)-1)
SET @sTable_Table = SUBSTRING(@sTable, @nSecondDot + 1, LEN (@sTable))
END
ELSE
-- Contains owner/table
IF @nFirstDot <> 0 and @nSecondDot = 0
BEGIN
SET @sTable_Owner = SUBSTRING(@sTable, 1, @nFirstDot - 1)
SET @sTable_Table = SUBSTRING(@sTable, @nFirstDot + 1, LEN (@sTable))
END
ELSE
-- Just table
SET @sTable_Table = SUBSTRING(@sTable, 1, LEN (@sTable))
SET @sSQLCursor = 'DECLARE COLUMN_CURSOR CURSOR FOR '
SET @sSQLCursor = @sSQLCursor + 'SELECT SC.NAME FROM '
SET @sSQLCursor = @sSQLCursor + @sTable_DB + '.' + @sTable_Owner + '.SYSOBJECTS SO, '
SET @sSQLCursor = @sSQLCursor + @sTable_DB + '.' + @sTable_Owner + '.SYSCOLUMNS SC '
SET @sSQLCursor = @sSQLCursor + 'WHERE '
SET @sSQLCursor = @sSQLCursor + 'SO.NAME=''' + @sTable_Table + ''' AND SO.XTYPE=''U'' AND '
SET @sSQLCursor = @sSQLCursor + 'SO.ID=SC.ID '
EXEC sp_executesql @sSQLCursor
OPEN COLUMN_CURSOR
SET @sSQL = 'UPDATE ' + @Stable + ' SET '
FETCH NEXT FROM COLUMN_CURSOR INTO @sColName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sSQL = @sSQL + ' ' + @sColName
SET @sSQL = @sSQL + '= LTRIM(RTRIM(' + @sColName + '))'
SET @sSQL = @sSQL + ','
FETCH NEXT FROM COLUMN_CURSOR INTO @sColName
END
CLOSE COLUMN_CURSOR
DEALLOCATE COLUMN_CURSOR
SET @sSQL = SUBSTRING(@sSQL, 1, LEN(@sSQL) - 1) -- Trim trailing comma
EXEC(@sSQL)
SET @nRC = @@ERROR
End_SP:
RETURN @nRC
END
August 31, 2005 at 1:18 pm
A word of caution, make sure you are doing this on varchar fields and not char. char fields always include trailing spaces to the defined length.
Steve
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply