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

Question

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?

I have updated my question with my need to execute a query against a table to find out which columns have data more than length 7. I hope I explained correctly for the requirement for which I need help. — Sujatha Rajesh, Jan 22 '19 at 13:34

Doug Coats · Answer 1 · 2019-01-21T13:43:11.233

11

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

edited Jan 21 '19 at 13:43

answered Jan 21 '19 at 13:21

Doug Coats

6,255
9
27
49

1

Change `LEN(Address)>=7` to `LEN(Address)>7` right? – Mad Dog Tannen Jan 21 '19 at 13:28
1

`SELECT explanation FROM doug_coats WHERE Comment IN ('fine', 'sure', 'whatever', 'lol')` :-) – Mad Dog Tannen Jan 21 '19 at 13:48
1

; with explanation AS (SELECT humor, jaunt, sass FROM sillyBehavriors WHERE bored=TRUE) SELECT * FROM explanation @KayNelson – Doug Coats Jan 21 '19 at 13:52

score 2 · Answer 2 · answered Jan 21 '19 at 13:21

2

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;

answered Jan 21 '19 at 13:21

Gordon Linoff

1,242,037
58
646
786

if you write unions instead of `||` (or just submit 3 selects sequentially), then it is in columns & I think, what @Sujata Rajesh needs. – xerx593 Jan 21 '19 at 13:29
Actually this query is required to find which columns have data of length more than 7. Hence I cannot use this query. This query must ran in big table which has more than 25 columns also. It should be dynamic one which can be run in any table. – Sujatha Rajesh Jan 21 '19 at 13:32
1

lolz, @DougCoats ... yeah, then you need something "procedural" and with "root access" ...and very "vendor specific" `@SujathaRajesh` – xerx593 Jan 21 '19 at 13:40

Daniel Brughera · Answer 3 · 2019-01-23T08:32:21.497

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

Hi, thanks for your answer. I am getting errors when running this query. Not sure if mistake is my side. I will check after resolving the problems and let you know. — Sujatha Rajesh, Jan 22 '19 at 10:22
Is it a procedure? What to do to execute this directly copying and pasting in my TOAD tool? — Sujatha Rajesh, Jan 22 '19 at 13:32
Is an script, you can run it directly or put it into a stored procedure, as you mentioned that you need it for large tables, it generates dynamic sql code so you can run it for any desired table, anyway I edited the answer to show you the generated code if yo prefer to use it directly — Daniel Brughera, Jan 23 '19 at 08:35

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

3 Answers3