longest character in a field - 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

SQL Server 2000 Forums

Transact-SQL (2000)

longest character in a field

Author

Topic

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-11-27 : 08:55:35

Hello,
how do i find the longest character in a column ?

thanks
Afrika

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-27 : 08:58:17

Longest word?

Start with

SELECT YourColumn, LEN(YourColumn), DATALENGTH(YourColumn)
FROM YourTable

SELECT TOP 1 YourColumn FROM YourTable ORDER BY LEN(YourColumn) DESC
SELECT TOP 1 YourColumn FROM YourTable ORDER BY DATALENGTH(YourColumn) DESC

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-27 : 09:25:45

Longest character? in terms of frequency of cccurrence?

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-27 : 09:36:12

Largest sequence of same character?

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-27 : 09:54:30

quote:
Originally posted by afrika

Hello,
how do i find the longest character in a column ?

thanks
Afrika

Each character has same length until you meant it a word

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-27 : 10:20:22

[code]DECLARE @s VARCHAR(200)

SELECT @s = 'aardvark'

-- ~~Most~~ Highest frequency of same character
SELECT TOP 1 WITH TIES
[char],
COUNT(*) cnt
FROM (
SELECT 1 + Number [num],
SUBSTRING(@s, 1+ Number, 1) [char]
FROM master..spt_values
WHERE Name IS NULL
AND Number < DATALENGTH(@s)
) q
GROUP BY [char]
ORDER BY COUNT(*) DESC[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-27 : 10:49:23

[code]DECLARE @s VARCHAR(200)

SELECT @s = 'aardvark'

-- Longest sequence of same character
SELECT TOP 1 q.[char],
q.f [sequence]
FROM (
SELECT SUBSTRING(@s, 1 + Number, 1) [char],
PATINDEX('%[^' + SUBSTRING(@s, 1+ Number, 1) + ']%', SUBSTRING(@s, 2 + Number, 8000)) f
FROM master..spt_values
WHERE Name IS NULL
AND Number < DATALENGTH(@s)
) q
ORDER BY q.f DESC[/code]

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-27 : 13:29:15

quote:

Originally posted by Peso

DECLARE	@s VARCHAR(200)

SELECT	@s = 'aardvark'

-- Longest sequence of same character
SELECT TOP 1	q.[char],
		q.f [sequence]
FROM		(
			SELECT	SUBSTRING(@s, 1 + Number, 1) [char],
				PATINDEX('%[^' + SUBSTRING(@s, 1+ Number, 1) + ']%', SUBSTRING(@s, 2 + Number, 8000)) f
			FROM	master..spt_values
			WHERE	Name IS NULL
				AND Number < DATALENGTH(@s)
		) q
ORDER BY	q.f DESC

Peter Larsson
Helsingborg, Sweden

This solution takes care of only adjacent characters, but not when character is distributed over the length of word.

for e.g.

when @s = 'aardvddrdkd' then even though 'd' - 4 and 'a' - 2, still the above solution returns 'a' as most frequent character.

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-27 : 13:36:42

My first suggestion takes care of highest frequency of character. That is "d" (4 items) in your sample data.
My second suggestion takes case of longest sequence of character. That is both "d" and "a" (2 items).

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-27 : 13:38:19

Or you can add SELECT TOP 1 WITH TIES ...

Peter Larsson
Helsingborg, Sweden

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-11-27 : 17:03:02

quote:
Originally posted by Peso

Largest sequence of same character?

Peter Larsson
Helsingborg, Sweden

Yes,

thanks great deal

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-11-27 : 18:53:12

quote:

Originally posted by Peso

DECLARE	@s VARCHAR(200)

SELECT	@s = 'aardvark'

-- Longest sequence of same character
SELECT TOP 1	q.[char],
		q.f [sequence]
FROM		(
			SELECT	SUBSTRING(@s, 1 + Number, 1) [char],
				PATINDEX('%[^' + SUBSTRING(@s, 1+ Number, 1) + ']%', SUBSTRING(@s, 2 + Number, 8000)) f
			FROM	master..spt_values
			WHERE	Name IS NULL
				AND Number < DATALENGTH(@s)
		) q
ORDER BY	q.f DESC

Peter Larsson
Helsingborg, Sweden

Really great work!

rockmoose

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-28 : 00:48:40

Thank you!
Hearing it from you, I'm convinced it is. But I doubt OP will understand the logic for some time.

I added WITH TIES just for clarification

DECLARE	@s VARCHAR(200)

SELECT	@s = 'aardddvaaaarrrrk'

-- Longest sequence of same character
SELECT TOP 1	WITH TIES
		q.[char],
		q.f [sequence]
FROM		(
			SELECT	SUBSTRING(@s, 1 + Number, 1) [char],
				PATINDEX('%[^' + SUBSTRING(@s, 1 + Number, 1) + ']%', SUBSTRING(@s, 2 + Number, 8000)) f
			FROM	master..spt_values
			WHERE	Name IS NULL
				AND Number < DATALENGTH(@s)
		) q
ORDER BY	q.f DESC

Not bad for a set-based solution

If you want numbers from 1 to 8000, use Michael Valentine Jones function here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685 instead of getting numbers from 0 to 255 from master..spt_values

I think this algorithm will be ok with TEXT datatype too.

Below is enhanced version with a bug fix too. The code above does not calculate longest sequence if sequence is rightmost characters.

DECLARE	@s VARCHAR(8000)

SELECT	@s = 'aardddvaaaarrkkkk'

-- Highest frequency of same character
SELECT TOP 1	WITH TIES
		[char],
		COUNT(*) cnt
FROM		(
			SELECT	SUBSTRING(@s, Number, 1) [char]
			FROM	F_TABLE_NUMBER_RANGE(1, 8000)
			WHERE	Number <= DATALENGTH(@s)
		) q
GROUP BY	[char]
ORDER BY	2 DESC
/*
SELECT TOP 1	WITH TIES
		SUBSTRING(@s, Number, 1) [char],
		COUNT(*) cnt
FROM		F_TABLE_NUMBER_RANGE(1, 8000)
WHERE		Number <= DATALENGTH(@s)
GROUP BY	SUBSTRING(@s, Number, 1)
ORDER BY	2 DESC
*/

-- Longest sequence of same character
SELECT TOP 1	WITH TIES
		[char],
		CASE WHEN [seq] = 0 THEN DATALENGTH(@s) - Number + 1 ELSE [seq] END [seq]
FROM		(
			SELECT	SUBSTRING(@s, Number, 1) [char],
				Number,
				PATINDEX('%[^' + SUBSTRING(@s, Number, 1) + ']%', SUBSTRING(@s, 1 + Number, 8000)) [seq]
			FROM	F_TABLE_NUMBER_RANGE(1, 8000)
			WHERE	Number <= DATALENGTH(@s)
		) q
ORDER BY	2 DESC
/*
SELECT TOP 1	WITH TIES
		SUBSTRING(@s, Number, 1) [char],
		CASE
			WHEN PATINDEX('%[^' + SUBSTRING(@s, Number, 1) + ']%', SUBSTRING(@s, 1 + Number, 8000)) = 0 THEN DATALENGTH(@s) - Number + 1
			ELSE PATINDEX('%[^' + SUBSTRING(@s, Number, 1) + ']%', SUBSTRING(@s, 1 + Number, 8000))
		END [seq]			
FROM		F_TABLE_NUMBER_RANGE(1, 8000)
WHERE		Number <= DATALENGTH(@s)
ORDER BY	2 DESC
*/

Peter Larsson
Helsingborg, Sweden

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-11-28 : 03:47:52

The "longest character" in Unicode is U+FDFA (ARABIC LIGATURE SALLALLAHOU ALAYHE WASALLAM) which has a decomposition to 18 characters.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-28 : 05:11:53

quote:
Originally posted by harsh_athalye

when @s = 'aardvddrdkd' then even though 'd' - 4 and 'a' - 2, still the above solution returns 'a' as most frequent character.

No.
And "d" actually is present 5 times

But it is good that you question the suggestion/solution!

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-28 : 06:21:37

quote:
And "d" actually is present 5 times

Oh..sorry! my mistake.

Anyways, but the solutions were simply brilliant.

I don't think there is anybody else here who use master..spt_values so nicely.

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-28 : 09:43:15

Oh, that. I am just being lazy...

Peter Larsson
Helsingborg, Sweden

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-11-28 : 16:26:58

Actually the name is null made me queasy

select number from spt_values where type = 'p'

Interestingly in sql2005, the p range is augmented to 256 bytes (2048 rows) from the measly 255 rows in sql2000.

And some new sql2005 number range hack could be:
select n = row_number() over(order by object_id) from sys.columns

Brilliant solutions, and a nice spot of the tail bug!

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2006-11-30 : 00:50:06

Peter,

Your solution for adjacent characters is simply brilliant... there is a fly in the ointment, though... Try this with your good formula...

SELECT @s = 'aardvarkkkk'

--Jeff Moden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-30 : 01:46:05

It gives me the result

char  seq
----  ---
k	4

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-30 : 01:53:59

Bugfixed version with master..spt_values

DECLARE	@s VARCHAR(8000)

SELECT	@s = 'aardddvaaaarrkkkk'

-- Highest frequency of same character
SELECT TOP 1	WITH TIES
		[char],
		COUNT(*) cnt
FROM		(
			SELECT	SUBSTRING(@s, 1 + Number, 1) [char]
			FROM	master..spt_values
			WHERE	Number < DATALENGTH(@s)
				AND Name IS NULL
		) q
GROUP BY	[char]
ORDER BY	2 DESC
/*
SELECT TOP 1	WITH TIES
		SUBSTRING(@s, 1 + Number, 1) [char],
		COUNT(*) cnt
FROM		master..spt_values
WHERE		Number < DATALENGTH(@s)
		AND Name IS NULL
GROUP BY	SUBSTRING(@s, 1 + Number, 1)
ORDER BY	2 DESC
*/

-- Longest sequence of same character
SELECT TOP 1	WITH TIES
		[char],
		CASE WHEN [seq] = 0 THEN DATALENGTH(@s) - Number  ELSE [seq] END [seq]
FROM		(
			SELECT	SUBSTRING(@s, 1 + Number, 1) [char],
				Number,
				PATINDEX('%[^' + SUBSTRING(@s, 1 + Number, 1) + ']%', SUBSTRING(@s, 2 + Number, 8000)) [seq]
			FROM	master..spt_values
			WHERE	Number < DATALENGTH(@s)
				AND Name IS NULL
		) q
ORDER BY	2 DESC
/*
SELECT TOP 1	WITH TIES
		SUBSTRING(@s, 1 + Number, 1) [char],
		CASE
			WHEN PATINDEX('%[^' + SUBSTRING(@s, 1 + Number, 1) + ']%', SUBSTRING(@s, 2 + Number, 8000)) = 0 THEN DATALENGTH(@s) - Number
			ELSE PATINDEX('%[^' + SUBSTRING(@s, 1 + Number, 1) + ']%', SUBSTRING(@s, 2 + Number, 8000))
		END [seq]			
FROM		master..spt_values
WHERE		Number < DATALENGTH(@s)
		AND Name IS NULL
ORDER BY	2 DESC
*/

Peter Larsson
Helsingborg, Sweden

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources