find the longest text in sql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

find the longest text in sql

Author

Topic

Madde
Starting Member

1 Post

Posted - 2013-01-22 : 16:08:30

hi everyone :]

i'm trying to find the longest text in my column row DNAME. i have succeded in doing so but i cant seem to show the longest text only the ammout of length:

SELECT MAX(CHAR_LENGTH(DNAME)) as 'Max length'
FROM DEPT;

this will give the answer

+------------+
| max length |
+------------+
| 10 |
+------------+

what i want to show is

+--------------+--------------+
| DNAME | Max LENGTH |
+--------------+--------------+
| ACCOUNTING | 10 |
+--------------+--------------
i tried with:

SELECT DNAME, MAX(CHAR_LENGTH(DNAME)) as 'Max length'
FROM DEPT HAVING MAX(CHAR_LENGTH(DNAME));

(this will only give the first DNAME in the column row, not the one with the longest length and the max length totally in the column row)

and

SELECT DNAME as 'Max length'
FROM DEPT HAVING MAX(CHAR_LENGTH(DNAME));

(this wil only give the first DNAME in the column row and not the one with the longest length)

I'm using putty :]
would appreciate any answer :]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-01-22 : 16:52:25

This isn't perfect (coudl return more than one row), but might get you going:

SELECT 
	DNAME as 'Max length'
FROM 
	DEPT 
WHERE
	CHAR_LENGTH(DNAME) = 
	(
		SELECT MAX(CHAR_LENGTH(DNAME))
		FROM DEPT
	)

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-23 : 00:16:44

[code]
SELECT
DNAME,
CHAR_LENGTH(DNAME) as 'Max length'
FROM
DEPT
WHERE
CHAR_LENGTH(DNAME) =
(
SELECT MAX(CHAR_LENGTH(DNAME))
FROM DEPT
)[/code]

This is SQL Server forum.. You may get quick response by posting MySql relarted posts in www.dbforums.com

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources