How to retrieve 2nd value of a user defined column in database

Question

How to retrieve 2nd value of a user defined column in database

Manish Kumar-832207

SSC Rookie

Points: 39
More actions
January 9, 2009 at 3:56 am

#203771

Suppose i have a table A with two columns x and y. x is of type number and y is user-defined-type. x is primary key. y is a array having element separated by ','.
I have samples in table A like below
x y
1 a,b,c,d,,,,,,,,,,,
2 a,a,c,c,,,,,,,,,,,
Now i am able to retrieve y as user-defined-type(a,b,c,d,,,,,,,,,,,) when i fire below query :-
select y from A where x=1;
But i want to get the value at position 2 i.e. 'b'.
Any help would be appreciated..

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

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

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

There are several ways of doing this. Can you explain a little more about the problem? For instance, how often do you want to do this? How large are the tables? How long are the column-delimited strings? How many elements? Does the number of elements vary between rows?

^{“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: 1004638 More actions · Answer 2

Of course, the best thing to do would be to normalize the table... even if it's just "temporary". Here's how to do that... then you can select from the result (not put into a table in this demo code) all day long without having to rebuild the wheel every time you need to use it... details are in the code comments...

--===== Create and populate a test table to demo the code with.

-- Note that this is NOT a part of the solution.

DECLARE @TableA TABLE (X INT PRIMARY KEY CLUSTERED, Y VARCHAR(8000))

-- Similar to the original requested data

INSERT INTO @TableA

(X,Y)

SELECT 1,'a,b,c,d' UNION ALL

SELECT 2,'a,c,c,b,f,a'

-- Monster string of unknown length

INSERT INTO @TableA

(X,Y)

SELECT 3, REPLICATE('a,b,c,d,e,f,g,h,i,j,k,LLLL,,NNNNNN',20)

-- Show what's in the test table.

SELECT * FROM @TableA

--===== Solve the problem in the form of a result that could be put into a table...

SELECT a.X,

t.N-LEN(REPLACE(LEFT(','+a.Y+',',t.N), ',', '')) AS Position,

SUBSTRING(','+a.Y,N+1,CHARINDEX(',',a.Y+',',N)-N) AS Y

FROM dbo.Tally t

INNER JOIN @TableA a

ON SUBSTRING(','+a.Y,t.N,1) = ','

AND N <= LEN(','+a.Y)

ORDER BY a.X, t.N

Now, for your next question that you're gonna ask... please see the following...

[font="Arial Black"]The "Numbers" or "Tally" Table: What it is and how it replaces a loop.[/font][/url]

Of course, if we were using SQL Server 2005/2008, we'd use ROW_NUMBER() to gen the positions. Calculating the positions like I did in the code is a bit slow (so far as I'm concerned) but there's only so many ways to skin this cat in 2k without getting into the RBAR of a UDF or other method...

And, Chris is very correct... if, for example, you knew that the individual "positions" were to always have just a single letter, the position calculation could be greatly optimized just by dividing the value of t.N by 2. If they were more than a single letter, but always the same number of letters, then you could simply divide by another number.

There's another way to do this in SQL Server 2000, of course. It does take a bit of code to set it up, but if you're working with a million such rows, we can really take advantage of the "running total" method to calculate the relative position of any element within a row. See the following for that method...

[font="Arial Black"]Solving the "Running Total" & "Ordinal Rank" Problems in SS 2k/2k5[/font][/url]

--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)

Dugi SSCoach Points: 17998 More actions · Answer 3

Once again the famous Jeff's table Tally table working wonderfull!

:w00t::cool::w00t:

Super solution Jeff!

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 4

:blush: Thanks, Dugi. Like I've said before and in the article, I didn't invent it... I just use the heck out of it. 😛

--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)

Dugi SSCoach Points: 17998 More actions · Answer 5

Ok Jeff there is the simple code from me...so your code is in advanced level 😛

SELECT

SUBSTRING(Y,3,1) AS Y -- this retrieve allways the second value inexample above!!!

FROM A

WHERE X =1

Have nice day!

:hehe:

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

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

Dugi (1/11/2009)
Ok Jeff there is the simple code from me...so your code is in advanced level 😛
SELECT
SUBSTRING(Y,3,1) AS Y -- this retrieve allways the second value inexample above!!!
FROM A
WHERE X =1
Have nice day!
:hehe:

haha that's why I was asking OP - this was coming next!

^{“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