-1

I work on SQL server 2012 I face issue :i can't get first max value from value Unit based on First value before comma .

as example This value Unit below :

1.89, 2.625, 3.465

I will get first value before comma separated as 1.89 then if this number is max value return full number exist on Value Unit

create table #finaltable
(
partid  int,
ValueUnit nvarchar(50)
)
insert into #finaltable(partid,ValueUnit)
values
(2532,'1.71, 2.375, 3.135'),
(2532,'1.89, 2.625, 3.465')
select * from #finaltable

How to get first max value from field ValueUnit based on first value before comma separated ?

Expected Result returned :

1.89, 2.625, 3.465

because 1.89 is maximum number from 1.71 then I returned full number

ahmed barbary
  • 628
  • 6
  • 21
  • 2
    Stop storing delimited data, and you won't have such a hard time. Especially with data like that, if you have a value like `N'10.12, 12.756, 15.423'` then `N'10.12'` could be the **lowest** of the first strings, not the highest. – Thom A Jun 29 '20 at 10:14
  • 1
    Agree with the above; you should seriously fix your data model and move away from storing CSV in your tables. If you _must_ store CSV, then let it just be static data; don't try to do math on it as you are attempting above. – Tim Biegeleisen Jun 29 '20 at 10:15
  • can you add another answer again I notice it is deleted so please can you re back added it – – ahmed barbary Jun 29 '20 at 12:30

1 Answers1

0

I agree with the comments, your design is bad. For more on that, you should also read "Is storing a delimited list in a database column really that bad?".

But well, you can use patindex() to get the position of the comma and then extract the first number representation with left(). convert() it to some decimal, order by it and take the TOP 1 row.

SELECT TOP 1
       *
       FROM #finaltable
       ORDER BY convert(decimal(4, 3), left(valueunit, patindex('%,%', valueunit) - 1)) DESC;

You may need to tweak the conversion to a decimal. I don't know what maximum length and precision you may need.

sticky bit
  • 36,626
  • 12
  • 31
  • 42
  • can you add another answer again I notice it is deleted so please can you re back added it – ahmed barbary Jun 29 '20 at 12:08
  • @ahmedbarbary: Sorry, I don't understand what you mean. I didn't delete this answer. Clearly, as otherwise you wouldn't be able to comment it... – sticky bit Jun 29 '20 at 19:53