Facebook Sign in | Join
Pushing database changes needn't be hard work with SQL Compare
Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.

TSQL Challenge #14 - Identify the longest sequence of characters in a string

This challenge is all about identifying sequences of characters in a string using a SET based query. The task is to identify the longest sequence of characters within a given string.

Here is the sample data for this challenge

Data
------------------------------------
9992EDC6-D117-4DEE-B410-4E5FAE46AE97
0BFC936B-BD9A-4C6A-AFB2-CF3F1752F8B1
4A73E7EB-7777-4A04-9258-F1E75097977C
5AAF477C-274D-400D-9067-035968F33B19
725DA718-30D0-44A9-B36A-89F27CDFEEDE
8083ED5A-D3B9-4694-BB04-F0B09C588888

Here is the expected output 

Data                                 Char Pos  Len 
------------------------------------ ---- ---- ----
8083ED5A-D3B9-4694-BB04-F0B09C588888 B    20   2 
8083ED5A-D3B9-4694-BB04-F0B09C588888 8    32   5 
4A73E7EB-7777-4A04-9258-F1E75097977C 7    10   4
4A73E7EB-7777-4A04-9258-F1E75097977C 7    34   2
9992EDC6-D117-4DEE-B410-4E5FAE46AE97 9    1    3
9992EDC6-D117-4DEE-B410-4E5FAE46AE97 1    11   2
9992EDC6-D117-4DEE-B410-4E5FAE46AE97 E    17   2
5AAF477C-274D-400D-9067-035968F33B19 A    2    2
5AAF477C-274D-400D-9067-035968F33B19 7    6    2
5AAF477C-274D-400D-9067-035968F33B19 0    16   2
5AAF477C-274D-400D-9067-035968F33B19 3    32   2
725DA718-30D0-44A9-B36A-89F27CDFEEDE 4    15   2
725DA718-30D0-44A9-B36A-89F27CDFEEDE E    33   2

Column "char" shows the character having longest sequence within the string. "pos" shows the starting position of the sequence and the "len" column shows the length of the sequence.

The output should be ordered as given in the 'expected output' listing. The data having the longest sequence should come on top followed by the next longest sequence (descending order). Within each data, the rows should be ordered by the position at which the sequence starts.

Sample Data

Use the following script to generate the sample data needed for this challenge 

DECLARE @t TABLE (Data VARCHAR(40) )

INSERT @t (Data) SELECT '9992EDC6-D117-4DEE-B410-4E5FAE46AE97'
INSERT @t (Data) SELECT '0BFC936B-BD9A-4C6A-AFB2-CF3F1752F8B1'
INSERT @t (Data) SELECT '4A73E7EB-7777-4A04-9258-F1E75097977C'
INSERT @t (Data) SELECT '5AAF477C-274D-400D-9067-035968F33B19'
INSERT @t (Data) SELECT '725DA718-30D0-44A9-B36A-89F27CDFEEDE'
INSERT @t (Data) SELECT '8083ED5A-D3B9-4694-BB04-F0B09C588888'

SELECT * FROM @t

Notes

  1. Read the Submission Guidelines and make sure that your solution follows them.
  2. The solution should work on SQL Server 2005, 2008 or later versions
  3. Use this forum for any questions related to TSQL Challenge #14

About the Authors.

Share

Related Posts

Michael DeFehr explains his winning solution to TSQL Challenge 14

Mark Cowne explains his solution to TSQL Challenge 14

TSQL Challenge 14 – Winning Solutions

TSQL Challenge 14 – Results of Basic Testing

Comments

# re: TSQL Challenge #14 - Identify the longest sequence of characters in a string

Thursday, October 01, 2009 3:46 AM by Jeff Moden

I'm in.  That was FUN!

# re: TSQL Challenge #14 - Identify the longest sequence of characters in a string

Thursday, October 01, 2009 5:06 AM by Peso

Good to see you here Jeff!

Welcome.

# re: TSQL Challenge #14 - Identify the longest sequence of characters in a string

Friday, October 02, 2009 3:58 AM by jonimatix

It was a good challenge :) Thanks

# re: TSQL Challenge #14 - Identify the longest sequence of characters in a string

Friday, October 02, 2009 7:20 AM by Kevan Riley

ermm not sure what I've done wrong, but I can't add replies in the forum.

# re: TSQL Challenge #14 - Identify the longest sequence of characters in a string

Friday, October 02, 2009 7:24 AM by Kevan Riley

ah ok - sorted it. I wasn't a member of the TSQLChallenge group!

# re: TSQL Challenge #14 - Identify the longest sequence of characters in a string

Saturday, October 03, 2009 12:50 AM by Sriram

Just Uploaded the solution script for TSQL Challenge #14

# re: TSQL Challenge #14 - Identify the longest sequence of characters in a string

Saturday, October 03, 2009 10:51 AM by Niladri Biswas

Hello

This is Niladri Biswas.

I have already send the solution to the TSQL Challenge team in the morning via mail.

And after that I was trying to upload the same via media gallery but was unable to do so after 3/4 times shot. Is there some problem in the server?

Also I asked in the mail about acknowledgement but didn't get.

The file name is niladri_biswas_tsqlchallenge_14.sql

And I send that from this mail id: niladri.biswas@sumerusolutions.com

Kindly acknowledge

Thank you

# re: TSQL Challenge #14 - Identify the longest sequence of characters in a string

Saturday, October 03, 2009 11:12 AM by Jacob Sebastian

Hi Niladri,

we have received your solution. Usually it will take some time before the acknowledgements are sent.

It is strange that you are not able to upload the files. I would suggest you try again next time. I will upload your solution to the media gallery shortly.

regards

Jacob

# re: TSQL Challenge #14 - Identify the longest sequence of characters in a string

Sunday, October 04, 2009 11:40 PM by Niladri Biswas

Hi Jacob,

Thanks for the timely response. Again in the morning after reaching the office I tried to do the same thing.

And I am receiving the same error message as under

"Sorry, there was a problem with your last request!

Either the site is offline or an unhandled error occurred. We apologize and have logged the error. Please try your request again or if you know who your site administrator is let them know too."

So please help me

Regards

Niladri

# re: TSQL Challenge #14 - Identify the longest sequence of characters in a string

Monday, October 05, 2009 11:16 PM by Jeff Moden

Peso wrote:

Good to see you here Jeff!

Welcome.

------------------------------------------------

Thanks Peter.

Dang... I just remembered... I still owe you a template.

# re: TSQL Challenge #14 - Identify the longest sequence of characters in a string

Tuesday, October 06, 2009 11:29 AM by Aurelien Verla

Hi Jacob, Peter,

Working on the challenge with Matthieu, we think that you should put another "kind" of line in the sample data, like => 0BFC936B-BB9A-4C6A-AFBB-CCCF175CCCB1

As you can see, there's two times the same "BB" group, and also two times "CCC".

My first attempt didn't manage that case and we think that the query should take care of it.

Regards.

# re: TSQL Challenge #14 - Identify the longest sequence of characters in a string

Tuesday, October 06, 2009 1:38 PM by Peso

That's why the sample data "4A73E7EB-7777-4A04-9258-F1E75097977C" is there, because of the double 7 groups.

# re: TSQL Challenge #14 - Identify the longest sequence of characters in a string

Wednesday, October 07, 2009 4:04 AM by Aurelien Verla

Hi Peso,

In the sample, they actually don't have the same length, 4 & 2.

What about a double group of the same length, as in my guid posted before ?

Should we only get out the first one ?

# re: TSQL Challenge #14 - Identify the longest sequence of characters in a string

Wednesday, October 07, 2009 4:18 AM by Aurelien Verla

As I really want to be understood, could you please tells us what sould be the results for the guid '0BFC936B-BB9A-4C6A-AFBB-CCCF175CCCB1'.

Do we have to match all the occurence:

0BFC936B-BB9A-4C6A-AFBB-CCCF175CCCB1 B 10 2

0BFC936B-BB9A-4C6A-AFBB-CCCF175CCCB1 B 22 2

0BFC936B-BB9A-4C6A-AFBB-CCCF175CCCB1 C 25 3

0BFC936B-BB9A-4C6A-AFBB-CCCF175CCCB1 C 32 3

Or only the first match of each same group based on char/length:

0BFC936B-BB9A-4C6A-AFBB-CCCF175CCCB1 B 10 2

0BFC936B-BB9A-4C6A-AFBB-CCCF175CCCB1 C 25 3

Thanks for helping us in writing the best query !

# re: TSQL Challenge #14 - Identify the longest sequence of characters in a string

Wednesday, October 07, 2009 4:28 AM by Peso

That would be your first suggestion. All groups should be displayed and the "0BFC936B-BB9A-4C6A-AFBB-CCCF175CCCB1" should be displayed after "4A73E7EB-7777-4A04-9258-F1E75097977C" and before "9992EDC6-D117-4DEE-B410-4E5FAE46AE97".

Because the longest sequence is three characters (4A7 has four as longest sequence), and there are four repetitive character groups (999 has only three groups).

# re: TSQL Challenge #14 - Identify the longest sequence of characters in a string

Wednesday, October 07, 2009 4:48 AM by Aurelien Verla

Thanks for your answer Peso.

I hope that everyone will check the comments to be aware of that case whom could change the query behavior.

# re: TSQL Challenge #14 - Identify the longest sequence of characters in a string

Wednesday, October 07, 2009 10:43 AM by Kevin Suchlicki

I think Niladri's problem with uploading to media gallery may have to do with browser.  I got same error with Chrome, but worked fine w/ IE.

# re: TSQL Challenge #14 - Identify the longest sequence of characters in a string

Wednesday, October 07, 2009 12:45 PM by Fabien "Waldar" Contaminard

As ksuchlicki I think the browser is guilty.

I couldn't upload with Chrome but it worked fine with Firefox.

# re: TSQL Challenge #14 - Identify the longest sequence of characters in a string

Wednesday, October 07, 2009 6:35 PM by reganwick

I did not see any specs on tie-breaker for GUIDs with the same max sequence numbers.

All of of these GUIDs have Len = 5

8083ED5A-D3B9-4694-BB04-F0B09C588888

8083ED5A-D3B9-4694-BB04-F0B0C588888Z

8083ED5A-D3B9-4694-BB04-F0B0C599999Z

7083ED5A-D3B9-4694-BB04-F0B0C599999Z

I have submiited a 2nd solution to sort cases such as this by

(1) pos of sequence

(2) if pos equal, then value of sequence chars

(3) if value of sequence equal, then GUID itself

These examples sort this way:

8083ED5A-D3B9-4694-BB04-F0B0C588888Z

7083ED5A-D3B9-4694-BB04-F0B0C599999Z

8083ED5A-D3B9-4694-BB04-F0B0C599999Z

8083ED5A-D3B9-4694-BB04-F0B09C588888

# re: TSQL Challenge #14 - Identify the longest sequence of characters in a string

Friday, October 09, 2009 12:56 AM by Parth Patel

Hi Peso,

I am really confused as I have already posted my solution a week ago and people have doubt about the problem.

Will you please explain the ordering in details? As far as I understood, the only ordering condition mentioned is "Group with the highest length of character sequence (Column denoting "Len") will come first in the ascending order of starting position of the sequence(Column denoting "Pos")."

Please tell me if I am mistaken understanding the problem. Moreover, is there any other thing we need to consider? If yes, then can you please provide us with the extended input and desired output?

Thanks in advanced.

# re: TSQL Challenge #14 - Identify the longest sequence of characters in a string

Friday, October 09, 2009 12:00 PM by ChssAddct

I see that Challenge 11, which had a submission deadline of July 27, still doesn't have winners posted. Let alone Challenge 13, which closed Sept. 21.

For SQL users accustomed to running set operations and getting results 'immediately', it's asking a lot that they must then wait around on mere humans to take their own sweet time to get around to choosing winners for previous challenges.

So just a heads up to all who get excited to spend your time coming up with a solution to the challenge, and your time perspective is on the order of seconds -- getting challenges 'closed out' takes place on the order of months.  It's a dramatic shift in time perspective.

# re: TSQL Challenge #14 - Identify the longest sequence of characters in a string

Friday, October 09, 2009 5:40 PM by Jacob Sebastian

Hi,

The results of challenge 11 will be announced early next week. We are trying hard to reduce the delay in evaluating and announcing the results of the challenges.

Thanks and we appreciate your feedback.

regards

Jacob

# re: TSQL Challenge #14 - Identify the longest sequence of characters in a string

Wednesday, October 28, 2009 6:38 PM by Madhangi

Hello all,

Challenge 14 was really challenging.  I know the time is up. I came to this site today and I am posting for the first time. But if you have time, please take a look at the solution.  I will be very happy hear your esteemed opinions.

Thanks & Regards,

Maddy

# re: TSQL Challenge #14 - Identify the longest sequence of characters in a string

Tuesday, April 06, 2010 2:39 PM by NetDefender

Easy

Copyright © Rivera Informatic Private Ltd.