-1

Lets say I have the following data in the Employee table: (nothing more)

  ID   FirstName   LastName         x
    -------------------------------------------------------------------
    20   John        Mackenzie          <A>te</A><b>wq</b><a>342</a><d>rt21</d>
    21   Ted         Green              <A>re</A><b>es</b><1>t34w</1><4>65z</4>
    22   Marcy       Nate               <A>ds</A><b>tf</b><3>fv 34</3><6>65aa</6>

I need to search in the X column and get highest number in <> these brackets

What sort of SELECT statement can get me, for example, the number 6 like in <6>, in the x column?

Nadeem_MK
  • 7,533
  • 7
  • 50
  • 61
user2783787
  • 1
  • 1
  • Is your requirement is fixed like you need records with <6> every time, or you are seeking something more generalized ? – Ashutosh Arya Sep 16 '13 at 12:05
  • 2
    Try to store that value in a different column, it's very difficult to work with the value like this. – davey Sep 16 '13 at 12:05
  • If you cannot get around having all those numbers embedded in strings in one column you probably would want to do a `RegEx` search for a pattern and select the maximul value from that, unfortunately SqlServer does not seem to have native support for that. You would need to write a `CLR-Function` as described here http://stackoverflow.com/a/8928436/2186023 – DrCopyPaste Sep 16 '13 at 12:10
  • @AshutoshArya Thanks for the quick reply. Yes I want a generalized solution for that. The above one was just an example. – user2783787 Sep 16 '13 at 12:11
  • @user2783787: And you are sure ! the string in column x will be like this only ??? – Ashutosh Arya Sep 16 '13 at 12:17
  • Options: (1) Write a function to do this. (2) Change your model to fit better into the relational model (how exactly do this is difficult to tell, as we don't know what your data means). – Bernhard Barker Sep 16 '13 at 12:19
  • @AshutoshArya: Yes The string in X column will be like this only – user2783787 Sep 17 '13 at 07:24
  • @user2783787: Please see the below answer it will work if you are having only one digit number in <>. else please let me know small tweak is needed.I will change the answer – Ashutosh Arya Sep 17 '13 at 07:27
  • @AshutoshArya: It can be any number and also there is no fixed position for this number. We need to select max number from all the records present in X column – user2783787 Sep 17 '13 at 08:30
  • @AshutoshArya: Below query is returning entire row but we need only the highest number... – user2783787 Sep 17 '13 at 09:36
  • please see the column x in you question it was different now, is it like this only ? – Ashutosh Arya Sep 17 '13 at 09:51
  • @AshutoshArya: Its same only, and yes its like that only – user2783787 Sep 17 '13 at 10:00
  • @user2783787 please use -1 in place of -9, and update me. – Ashutosh Arya Sep 17 '13 at 10:03
  • @AshutoshArya: its returning second row(id=21) which is not correct.Previous one is correct only but it is returning whole row instead of maximum number. – user2783787 Sep 17 '13 at 10:08
  • can you paste me the sample column x, 2-3 records ? – Ashutosh Arya Sep 17 '13 at 10:09
  • @AshutoshArya: above are 3 sample records contain id and x column – user2783787 Sep 17 '13 at 10:27
  • do you see any pattern in this, i am sorry. its impossible to code this without considering all the scenario. I would request you to spend some time to analyze this data. for example column starting with 113255 which number would you consider to sort there is,4,6,7,8 in <> you first identify this. and then only this can be coded. – Ashutosh Arya Sep 17 '13 at 10:33

1 Answers1

0

This type of query generally works on finding patterns, I consider that the <6> is at the 9th position from left.

Please note if the pattern changes the below query will not work.

SELECT A.* FROM YOURTABLE A INNER JOIN
(SELECT TOP 1 ID,Firstname,Lastname,SUBSTRING(X,LEN(X)-9,1) AS [ORDER]
 FROM YOURTABLE
 WHERE ISNUMERIC(SUBSTRING(X,LEN(X)-9,1))=1
 ORDER BY SUBSTRING(X,LEN(X)-9,1))B
ON
A.ID=B.ID AND
A.FIRSTNAME=B.FIRSTNAME AND
A.LASTNAME=B.LASTNAME
Ashutosh Arya
  • 1,138
  • 2
  • 8
  • 14