String function

Question

Post reply

String function

khushbu

SSCarpal Tunnel

Points: 4591
More actions
September 10, 2008 at 4:38 am

#71908

Hi
I have a table which has column that contains data in range and csv format.
Like
table1
col1 col2 col3..........
a 6-10,4,5 hjkj..............
b 0 81 uiu....
c 5-16,3,6,45 jhlkjlk........
I want to select the greatest no from the col2 column
like where col1= a the result would be 10 (it being greatest in all no's)
where col1= b the result is 81
col1= c result is 45
can somebody pls help me

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Yep, you need a tally table:

[font="Courier New"]DROP TABLE #Temp

-- make some sample data

CREATE TABLE #Temp (col1 CHAR(1), col2 VARCHAR(20), col3 VARCHAR(20))

INSERT INTO #Temp (col1, col2, col3)

SELECT 'a', '6-10,4,5', 'hjkj..............' UNION ALL

SELECT 'b', '0 81', 'uiu....' UNION ALL

SELECT 'c', '5-16,3,6,45', 'jhlkjlk........'

SELECT * FROM #Temp -- sanity check

DECLARE @Delimiter CHAR(1)

SET @Delimiter = ','

SELECT col1, MAX(CAST(SplitCol2 AS INT) ) AS MaxCol2

FROM (SELECT col1, SUBSTRING(REPLACE(REPLACE(col2, '-', ','), ' ', ',')+@Delimiter, number,

CHARINDEX(@Delimiter, REPLACE(REPLACE(col2, '-', ','), ' ', ',')+@Delimiter, number) - number) AS SplitCol2

FROM Numbers n, #Temp

WHERE number <= LEN(REPLACE(REPLACE(REPLACE(col2, '-', ','), ' ', ','),' ','|'))

AND SUBSTRING(@Delimiter + REPLACE(REPLACE(col2, '-', ','), ' ', ','), number, LEN(REPLACE(@Delimiter,' ','|'))) = @Delimiter

) d

GROUP BY col1

ORDER BY col1

[/font]

Results:

col1 MaxCol2

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

a 10

b 81

c 45

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004635 More actions · Answer 2

Well done, Chris! And, no bloody UDF, either!

khushbu,

If you don't know what a Tally table is or does, please refer to the following article. I believe you'll find it helpfull...

http://www.sqlservercentral.com/articles/TSQL/62867/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Thanks Jeff, and thanks also for posting up your tally table article. One of these days I'll take the time to put them in my sig

INSERT INTO sig SELECT Tally Table article, Best Practices article...

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

khushbu SSCarpal Tunnel Points: 4591 More actions · Answer 4

Thanks a lot Chris!

But the here posted code was just a snippet

i really dont know how many rows can be present(i'm selecting them on the basis of id and tht id can have 1 or even n rows)

also the value of col2= 1-5,7,8,67..

this can go on n on .. I dont know how many comma the cell contains.

what should be the approach then?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Hi Khushbu

You're welcome, thanks for the feedback.

The number of rows will probably matter if there are hundreds of thousands. If the query runs too slowly (and it runs in minutes on a million rows), you could always batch it with a WHERE clause.

The number of elements in each cell matters - but as long as the largest number in your tally table exceeds the maximum number of elements, you'll be ok.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004635 More actions · Answer 6

Chris Morris (9/10/2008)
Hi Khushbu
You're welcome, thanks for the feedback.
The number of rows will probably matter if there are hundreds of thousands. If the query runs too slowly (and it runs in minutes on a million rows), you could always batch it with a WHERE clause.
The number of elements in each cell matters - but as long as the largest number in your tally table exceeds the maximum number of elements, you'll be ok.
Cheers
ChrisM

Actually, the split is based on the number of characters, not the number of elements (unless you meant characters and I'm just being semantical;)). And, since this is SQL Server 2000, I also suspect the largest number of characters you'll need is 8,000 to support a VARCHAR(8000). My Tally table is a nice whole 11,000 because it'll make more than 30 years of days for some financial calculations.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004635 More actions · Answer 7

Chris Morris (9/10/2008)
Thanks Jeff, and thanks also for posting up your tally table article. One of these days I'll take the time to put them in my sig
INSERT INTO sig SELECT Tally Table article, Best Practices article...
Cheers
ChrisM

Best compliment a person could get... thanks Chris.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110209 More actions · Answer 8

khushbu.kumar (9/10/2008)
Thanks a lot Chris!
But the here posted code was just a snippet
i really dont know how many rows can be present(i'm selecting them on the basis of id and tht id can have 1 or even n rows)
also the value of col2= 1-5,7,8,67..
this can go on n on .. I dont know how many comma the cell contains.
what should be the approach then?

Did idea of building a relational database cross your mind any time?

Database will be way more effective than any text file processing tool you may invent.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004635 More actions · Answer 9

Sergiy (9/10/2008)
khushbu.kumar (9/10/2008)
Thanks a lot Chris!
But the here posted code was just a snippet
i really dont know how many rows can be present(i'm selecting them on the basis of id and tht id can have 1 or even n rows)
also the value of col2= 1-5,7,8,67..
this can go on n on .. I dont know how many comma the cell contains.
what should be the approach then?
Did idea of building a relational database cross your mind any time?
Database will be way more effective than any text file processing tool you may invent.

I gotta agree with Sergiy... now that you've seen what a pain in the patooti having such ugly data in a database can do to you, why don't you suggest that the table and the data be normalized a bit? The Tally table can do it in a flash just like Chris showed except you wouldn't find the Max... you'd insert into a new table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

Jeff Moden (9/10/2008)
Chris Morris (9/10/2008)
Hi Khushbu
You're welcome, thanks for the feedback.
The number of rows will probably matter if there are hundreds of thousands. If the query runs too slowly (and it runs in minutes on a million rows), you could always batch it with a WHERE clause.
The number of elements in each cell matters - but as long as the largest number in your tally table exceeds the maximum number of elements, you'll be ok.
Cheers
ChrisM
Actually, the split is based on the number of characters, not the number of elements (unless you meant characters and I'm just being semantical;)). And, since this is SQL Server 2000, I also suspect the largest number of characters you'll need is 8,000 to support a VARCHAR(8000). My Tally table is a nice whole 11,000 because it'll make more than 30 years of days for some financial calculations.

Oops my bad - and it matters! Thanks Jeff.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

khushbu SSCarpal Tunnel Points: 4591 More actions · Answer 11

Jeff Moden (9/10/2008)
Sergiy (9/10/2008)
khushbu.kumar (9/10/2008)
Thanks a lot Chris!
But the here posted code was just a snippet
i really dont know how many rows can be present(i'm selecting them on the basis of id and tht id can have 1 or even n rows)
also the value of col2= 1-5,7,8,67..
this can go on n on .. I dont know how many comma the cell contains.
what should be the approach then?
Did idea of building a relational database cross your mind any time?
Database will be way more effective than any text file processing tool you may invent.
I gotta agree with Sergiy... now that you've seen what a pain in the patooti having such ugly data in a database can do to you, why don't you suggest that the table and the data be normalized a bit? The Tally table can do it in a flash just like Chris showed except you wouldn't find the Max... you'd insert into a new table.

Ahh!! I have told these ppl 1000 times to let me do the normalization but since ppl here are lazy to do the rework on application, queries,sp they wont let me do anything which is good

I had fought with them many times everytime they give silly reason for this and even after explaining them the pain and advantages they wont let me do anything.

And the silly part they ask a DBA to write queries and height of insanity even manager agrees to it.

Anyway thx for the feedback. I wish I could hammer such ppl...

Jeff Moden SSC Guru Points: 1004635 More actions · Answer 12

On the other hand, look at the bright side... 🙂 They have such a crappy DB that they need someone like you to wade through it and fix things. If you do it with a smile, you become indispensable. If you write these types of "saves" down as they occur in a log, then it comes to review time, you attach the log with the words "Look what I've done to keep you from spending extra money and time... could I please have a decent merit increase?" 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

khushbu SSCarpal Tunnel Points: 4591 More actions · Answer 13

Jeff Moden (9/11/2008)
On the other hand, look at the bright side... 🙂 They have such a crappy DB that they need someone like you to wade through it and fix things. If you do it with a smile, you become indispensable. If you write these types of "saves" down as they occur in a log, then it comes to review time, you attach the log with the words "Look what I've done to keep you from spending extra money and time... could I please have a decent merit increase?" 😛

Thanks for the motivation!!