Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

Compare numbers stored as characters seperated by space

May 15 2012 12:00AM by Madhivanan   

In SQL forums, one of the memebers asked this question.

"In the table a varchar column has values like '2.2020 30 4.0000', '2.20200 30 4.00', etc. When I pass a parameter with the value '2.202 30 4.0' , the above value should be returned as output. How do I do it?"

(Note that 2.2020  and 2.20200 from column value and 2.202 from parameter value are same when compared as Numeric values)

Well. My first advice is not to store multiple values as characters in the single column. Normalization should be applied. If there is no chance to modify the existing structure, there are two ways to compare

1 Split the column value and parameter value by space and compare each number (This whill be most suggested as usual)

2 Do not use any split function. Make some changes in the column and parameter to effectively compare the value.

Ok. We will see how to implement Method 2

Consider the following set of data

 

declare @t table(col varchar(100))
insert into @t
select	'2.2020 30 4.0000' union all
select	'2.2020 30 4.05000' union all
select	'2.20200 30 4.00200' union all
select	'2.2020 300 4.00' union all
select	'2.202000 30 4.0000' 


declare @s varchar(100),@sql varchar(max)
set @s= '2.202 30 4.0' 
set @sql=''
select 
	@sql=@sql+
		'select '''+col+''' as original_col,sum('+replace(col,' ','+')+') as col1,sum('+replace(@s,' ','+')+') as col2,'''+@s+''' as variable_val union all ' 
from @t
select @sql='select original_col as col_val,variable_val from ('+left(@sql,len(@sql)-11)+') as t where col1=col2 and replace(replace(original_col,''0'','' ''),'' '','''')=replace(replace(variable_val,''0'','' ''),'' '','''')'
exec(@sql)
 
Now look at the result
col_val            variable_val
------------------ ------------
2.2020 30 4.0000   2.202 30 4.0
2.20200 30 4.00    2.202 30 4.0
If you have any other method please post it in comment's section

Tags: 


Madhivanan
3 · 40% · 12924
6
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 



Submit

14  Comments  

  • 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
    
    
    
    
    commented on May 15 2012 7:22AM
    Zibi
    25 · 6% · 2030
  • Zubi, Thanks. But it gives the following error

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

    commented on May 15 2012 7:34AM
    Madhivanan
    3 · 40% · 12924
  • That's a nice approach, Madhivanan! Quite frankly, I had never thought about it. Thank-you very much for sharing.

    commented on May 15 2012 11:04AM
    Nakul Vachhrajani
    4 · 36% · 11624
  • 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
    
    commented on May 15 2012 11:36AM
    Zibi
    25 · 6% · 2030
  • 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.

    commented on May 15 2012 2:05PM
    ErikEckhardt
    65 · 3% · 898
  • 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". ;-)

    commented on May 15 2012 5:41PM
    Jeff Moden
    159 · 1% · 305
  • 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.

    commented on May 15 2012 6:26PM
    ErikEckhardt
    65 · 3% · 898
  • Zibi, I have modified the code (added a condition in the where clause) Please check with some more data

    commented on May 16 2012 3:14AM
    Madhivanan
    3 · 40% · 12924
  • ErikEckhardt, I already tried that but can't get it right without a loop so I used SUM approach

    commented on May 16 2012 3:15AM
    Madhivanan
    3 · 40% · 12924
  • Jeff Moden, Thanks for the comment. I hope you will have some other approch with Tally number table :)

    commented on May 16 2012 3:16AM
    Madhivanan
    3 · 40% · 12924
  • 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.

    commented on May 16 2012 7:27AM
    Shivendra Kumar Yadav
    60 · 3% · 936
  • @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.

    commented on May 16 2012 7:38PM
    ErikEckhardt
    65 · 3% · 898
  • 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 
    
    
    commented on May 17 2012 2:10AM
    Zibi
    25 · 6% · 2030
  • 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.

    commented on May 17 2012 1:49PM
    ErikEckhardt
    65 · 3% · 898

Your Comment


Sign Up or Login to post a comment.

"Compare numbers stored as characters seperated by space" rated 5 out of 5 by 6 readers
Compare numbers stored as characters seperated by space , 5.0 out of 5 based on 6 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]