Hello, another approuch using xml (splitting numbers, normalizing and creating key from them - those action perform in searched string and in the table. Next seeking prepared key in the table) regards Zibi

;with cte01 as ( --normalizing in searched string
  select S=@s
         ,X1=cast(convert(decimal(10,6), cast(x.query('/a[1]/text()') as varchar)) as varchar)+ ' '
            +cast(convert(decimal(10,6), cast(x.query('/a[2]/text()') as varchar)) as varchar)+ ' '
            +cast(convert(decimal(10,6), cast(x.query('/a[3]/text()') as varchar)) as varchar)+ ' '
  from
  (select s=1)Q
   CROSS APPLY ( SELECT x=cast(''+replace(@s, ' ', '')+''  as xml)) a(x)
)
, cte02 as ( --normalizing in the table
   select col 
         ,X1=cast(convert(decimal(10,6), cast(x.query('/a[1]/text()') as varchar)) as varchar)+ ' '
            +cast(convert(decimal(10,6), cast(x.query('/a[2]/text()') as varchar)) as varchar)+ ' '
            +cast(convert(decimal(10,6), cast(x.query('/a[3]/text()') as varchar)) as varchar)+ ' '
   from @t
    cross apply (select cast(''+replace(col, ' ', '')+'' as xml)) a(x)


) 
select  col_val=col   --look for prepared key
       ,variable=@s 
from cte02 C2
  join cte01 C1 on C1.X1=C2.X1

Zubi, Thanks. But it gives the following error

Msg 8114, Level 16, State 5, Line 15 Error converting data type varchar to numeric.

That's a nice approach, Madhivanan! Quite frankly, I had never thought about it. Thank-you very much for sharing.

Hello, not happy with data like below

 
set @s= '2.202 3.0 31' 

returns:

2.2020 30 4.0000    2.202 3.0 31
2.202000 30 4.0000  2.202 3.0 31

and should return nothing

Using sum is not safe. Add this to see what I mean:

union select  '2.201 30 4.0100'

Instead, start with repeatedly trimming trailing zeroes until none are left - replace(@string + ' ', '0 ', ' ')

Then trim trailing decimal points - replace(@string + ' ', '. ', '')

Then compare.

Wonderful ideas (seriously!) but I'd recommend the proper normalization of the data that Madhivanan first recommended instead of continuing the falacy of using a VARCHAR column to store this information. Can't change the table because it's a 3rd party table? I would seriously consider adding persisted calculated columns if at all possible and a parallel table if no changes were allowed. I'd also recommend finding a new 3rd party poswsible taking the current one out for a nice porkchop dinner "Jeff Moden Style". ;-)

Jeff, all of us cringe at seeing first normal form violated. I shouldn't have corrected the flaw I saw without also mentioning the bigger flaw you so rightly decry.

Zibi, I have modified the code (added a condition in the where clause) Please check with some more data

ErikEckhardt, I already tried that but can't get it right without a loop so I used SUM approach

Jeff Moden, Thanks for the comment. I hope you will have some other approch with Tally number table :)

It's good way to incounter this senario madhivanan Really; but is it possible in real time senario.If it is so than we have to separate the columns it guess.

@Madhivanan, My example was not quite good enough to show you the problem.

Please try the following to see the incorrect value being included via the use of sum():

select  '2.2020 30 4.0200' union all
select  '2.2020 30 4.07000' union all
select  '2.20200 30 4.02200' union all
select  '2.2020 300 4.02' union all
select  '2.202000 30 4.0200' 
union all select  '2.220 30 4.002'

set @s= '2.202 30 4.02'

Looping is required to deal with this problem, unless you can guarantee a finite number of trailing zeroes and simply add in enough replace() operations to account for them.

Storing multiple values in a single column is utterly awful, violating the very first principle of good database design! The difficulty you're having in working with the data is a perfect proof of why this is such an abominable practice.

Split to multiple columns, or to a child table.

If this is absolutely not possible (shudder), and you are sure to only have 3 values, you are much better off extracting the three pieces, converting them to decimal, and comparing directly.

If the number of items is variable, then you are back to looping.

Good luck.

Hello, so below two more generic solutions based on tally table. The first in pure sql, second with a little xml support. Solutions base on normalisation (converting to decimal) and comparing (text or float). Both solutions are single queries.

--First
;with tally as ( --ad hoc tally table from 1 to 100, could be in easy way expanded to greater size
  select N=row_number() over (order by (select 1))  
  from
  (values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)
  )A(N)
  cross join (
    select N 
     from
     (values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)
      )B(N)
   )B(N)
)
, cte00 as (--prepare row for splitting
   select  col=' '+col+' '
          ,rn=row_number() over (order by (select 1))
   from @t
)
, cte01 as (--finding separate strings (splitting with tally table)
  select  rn
         ,col=col+' '
         ,N1=N+1
         ,N2=charindex(' ', col, N+1) -1
  from cte00
    join tally on 1=1  
                  and N<=len(col)+1 
                  and substring(col+' ', N, 1)=' ' 

)
, cte02 as (--converting strings to numbers
  select  rn
         ,rn2=row_number() over (partition by rn order by N1)
         ,col
         ,w=convert(decimal(10,7), substring(col, N1, N2-N1+1))
  from cte01
  where N2>0
)
---the same as above, but performed on @s variable
--cte's with simple exceptions copied from above
, scte00 as (
   select  col=' '+@s+' '
          ,rn=1
)
, scte01 as (
  select  rn
         ,col=col+' '
         ,N1=N+1
         ,N2=charindex(' ', col, N+1) -1
  from scte00
    join tally on 1=1  
                  and N<=len(col)+1 
                  and substring(col+' ', N, 1)=' ' 

)
, scte02 as (
  select  rn
         ,rn2=row_number() over (partition by rn order by N1)
         ,col
         ,w=convert(decimal(10,7), substring(col, N1, N2-N1+1))
  from scte01
  where N2>0
)
select  col_val=col
       ,variable_val=variable_val
from
(         
  select distinct c.rn
                ,C.col
                ,variable_val=S.col 
  from cte02 C
   join scte02 S on   1=1
                  and C.rn2=S.rn2
                  and C.W=S.W          
                  and (select max(rn2) from cte02)=(select count(*) from cte02 C2
                                                      join scte02 S2 on     1=1
                                                                      and C2.rn2=S2.rn2
                                                                      and C2.W=S2.W  
                                                     where C2.rn=C.rn                             
                         )        
)A
order by rn

--Second
;with tally as ( --ad hoc tally table from 1 to 100, could be in easy way expanded to greater size
  select N=row_number() over (order by (select 1))  
  from
  (values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)
  )A(N)
  cross join (
    select N 
     from
     (values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)
      )B(N)
   )B(N)
)
, cte00 as (--prepare row for splitting
   select  col=' '+col+' '
          ,rn=row_number() over (order by (select 1))
   from @t
)
, cte01 as (--finding separate strings (splitting with tally table)
  select  rn
         ,col=col+' '
         ,N1=N+1
         ,N2=charindex(' ', col, N+1) -1
  from cte00
    join tally on 1=1  
                  and N<=len(col)+1 
                  and substring(col+' ', N, 1)=' ' 

)
, cte02 as (--converting strings to numbers
  select  rn
         ,rn2=row_number() over (partition by rn order by N1)
         ,col
         ,w=convert(decimal(10,7), substring(col, N1, N2-N1+1))
  from cte01
  where N2>0
)
, cte03 as (--preparing keys from normalized fields
  select  rn
         ,col 
         ,x1=(select cast(w as varchar)+' ' from cte02 C2 where C2.rn=cte00.rn order by rn2 for xml path(''))
  from cte00  
)
---the same as above, but performed on @s variable
--cte's with simple exceptions copied from above
, scte00 as (
   select  col=' '+@s+' '
          ,rn=1
)
, scte01 as (
  select  rn
         ,col=col+' '
         ,N1=N+1
         ,N2=charindex(' ', col, N+1) -1
  from scte00
    join tally on 1=1  
                  and N<=len(col)+1 
                  and substring(col+' ', N, 1)=' ' 

)
, scte02 as (
  select  rn
         ,rn2=row_number() over (partition by rn order by N1)
         ,col
         ,w=convert(decimal(10,7), substring(col, N1, N2-N1+1))
  from scte01
  where N2>0
)
, scte03 as (
  select  rn
         ,col 
         ,x1=(select cast(w as varchar)+' ' from scte02 C2 where C2.rn=scte00.rn order by rn2 for xml path(''))
  from scte00  
)
select  col_val=c.col --finding records with the same normalised keys
       ,variable_val=s.col 
from cte03 c
  join scte03 s on c.x1=s.x1 

Here's my best solution.

;WITH Scrubbed AS (
    (
    	SELECT 'Col' Which, Col, Convert(varchar(max), '') Canonized, 0 NextPos FROM @t		
    	UNION ALL SELECT 'Val', @s, '', 0
    ) 
    UNION ALL SELECT
    	Which,
    	Col,
    	Canonized + ' ' + Convert(varchar(max), Convert(decimal(38, 10), Substring(Col, NextPos, IsNull(NullIf(CharIndex(' ', Col + ' ', NextPos), 0), Len(Col)) - NextPos))),
    	CharIndex(' ', Col, NextPos) + 1 NextPos
    FROM Scrubbed
    WHERE NextPos <> 1
), Final AS (
    SELECT Which, Col, Max(Canonized) Canonized
    FROM Scrubbed
    GROUP BY Which, Col
)
SELECT Col
FROM Final
WHERE
    Which = 'Col'
    AND Canonized = (SELECT Canonized FROM Final WHERE Which = 'Val')

My idea of replacing '0 ' with ' ' will not work because numbers without decimal points such as '30' get mutilated.