Geek asks and answers

How to compare cropped and uncropped field lengths without re-entering the field name?

Basically, I want to check if there are any spaces to the right of the text in the varchar field. For this, I want to compare DATALENGTH(fieldName)with DATALENGTH(RTRIM(fieldName)).

It is not that difficult.

The only thing I need to do for all varchar fields in five tables. It goes into about 250 fields, which I need to compare in this way. Is there a way that I can put all of these field names in one query without entering each 2x name?

I use this query to get the names of the fields that I need to look at.

SELECT name FROM sys.columns WHERE object_id = 
    (SELECT object_id FROM sys.tables WHERE name='tableName') 
AND system_type_id = 167

(using SQL Server 2005)

Thank!

+3
source share
4 answers

SQL Server , , :

SELECT
    'SELECT * FROM ' + o.name + ' WHERE DATALENGTH(' + c.name + ') > DATALENGTH(RTRIM(' + c.name + '))'
FROM
    sys.objects o
INNER JOIN sys.columns c ON
    c.object_id = o.object_id AND
    c.system_type_id = 167
WHERE
    o.name = 'tableName'

, 167 - , ?

+2

, SQL, , SQL, . Notepad ++ .

  • ,
  • Ctrl + R
  • ^ (. +) $
  • "\ 1LenMatch AS CASE DATALENGTH (\ 1) = DATALENGTH (RTRIM (\ 1)) THEN 1 ELSE 0 END" sql, , \1, , .
0

, , SQL Server .

Select ' Or DataLength(' + Name + ')<>DataLength(RTrim(' + Name + '))'
  From sys.Columns
 Where Object_ID=Object_ID('Employees')
   And System_Type_ID=167

( , sys.tables, Object_ID !)

, , , , , . .

Create Function Dbo.EndsWithSpaces(@Value Varchar(8000)) Returns Bit
As Begin
  Return Case When DataLength(@Value)=DataLength(RTrim(@Value)) Then 0
              Else 1 End
End

: Dbo.EndsWithSpaces(Value)=1 , SQL Server Where:

Select ' Or Dbo.EndsWithSpaces(' + Name + ')=1'
  From sys.Columns
 Where Object_ID=Object_ID('Employees')
   And System_Type_ID=167
0

- . 1, . 0, . 1, , .

:

Select Columns...
From   Table
Where  CharIndex(' ', Reverse(ColumnName)) = 1
0

More articles:


All Articles