CODEWITHSUNDEEP
sqlmultiple-columnscontent-length
Sujatha Rajesh
Sujatha Rajesh

Reputation: 109

how to select Columns using SQL which has data length greater than given length

I have a table with specific columns and rows. I would like to select columns which has data more than length 7.

For ex: Table has columns

Name      Address   PhoneNumber
AAA       AAAAAAAA   12345678
BBBBB     BBBBBBB    47854
CCC       FFFF       76643

Here columns 'Address ' and 'Phone Number' has data length more than 7. So it should display,

Address
PhoneNumber

as results. This is for a particular table. Here I do not know already that Address and PhoneNumber are the columns which have data greater than length 7. Only from the query result I will be able to find it.

SELECT <<all_columns>> from table where length(columns)>7 is my input requirement.

The LENGTH or LEN functions in 'Where' clause gives option to give only one specific column name

instead of LENGTH(COL_NAME) , I need option as where LENGTH(<> or something like LENGTH(*)) > 7 should be given as input.

How that can be achieved?

Upvotes: 9

Views: 67467

Answers (3)

Daniel Brughera
Daniel Brughera

Reputation: 1651

As I read you need a dynamic sql for larger tables than your example (that should be part of your question)

I used unpivot to compare all lengths at once 

DECLARE @TableName VARCHAR(100) = 'YourTableName'
DECLARE @MaxLen INT = 7

DECLARE @Definition 
    TABLE (
        ColumnName VARCHAR(50)
    )

INSERT @Definition
SELECT C.Name 
FROM 
sys.columns C
JOIN sys.tables T
ON C.object_id = T.object_id
WHERE t.name = @TableName

DECLARE @Columns VARCHAR(MAX) = ''
DECLARE @ColumnsWithCast VARCHAR(MAX) = ''

SET @Columns = STUFF(
                    (SELECT ',' + ColumnName
                     FROM     @Definition
                     FOR XML PATH('')
                    ), 
                    1, 
                    1, 
                    '')

SET @ColumnsWithCast = STUFF(
                    (SELECT ',CAST(' + ColumnName + ' AS VARCHAR(MAX)) AS ' + ColumnName
                     FROM     @Definition
                     FOR XML PATH('')
                    ), 
                    1, 
                    1, 
                    '')

DECLARE @SQL NVARCHAR(MAX) = N'
SELECT DISTINCT 
    Field
FROM (
        SELECT 
            ' + @ColumnsWithCast + '  
        FROM ' + @TableName + ' A 
) p  
UNPIVOT (
    Value FOR Field IN (
        ' + @Columns + '
    )  
)AS unpvt
WHERE LEN(Value) > @MaxLen
'

DECLARE @ParamDefinition NVARCHAR(100) = N'@MaxLen INT'

EXEC sp_executesql @SQL, @ParamDefinition, @MaxLen = @MaxLen

It will generate this code with all the existing columns

SELECT DISTINCT 
    Field
FROM (
        SELECT 
            CAST(Name AS VARCHAR(MAX)) AS Name, 
            CAST(Address AS VARCHAR(MAX)) AS Address,
            CAST(PhoneNumber AS VARCHAR(MAX)) AS PhoneNumber,  
        FROM HIERARCHY A 
) p  
UNPIVOT (
    Value FOR Field IN (
        Name, Address, PhoneNumber
    )  
)AS unpvt
WHERE LEN(Value) > @MaxLen

Upvotes: 0

Doug Coats
Doug Coats

Reputation: 7107

So HAVING is probably the clause youd want to use. Obviously, you can expand to include all columns and increase the having. see this:

 SELECT 
      Name,
      Address, 
      Phonenumber, 
      LEN(Address) AS AddyLength
 FROM
      yourTables
 GROUP BY
      Name,
      Address, 
      Phonenumber, 
 HAVING
      LEN(Address)>7

Upvotes: 12

Gordon Linoff
Gordon Linoff

Reputation: 1269873

If you can live with the results in columns rather than rows:

select (case when max(length(name)) > 7 then 'Name;' else '' end) ||
       (case when max(length(address)) > 7 then 'address;' else '' end) ||
       (case when max(length(phone)) > 7 then 'phone;' else '' end) 
from t;

Upvotes: 2

Related Questions