longest match starting digit.

• Jawad Ahmed Siddiqui

  Old Hand

  Points: 301

  More actions

  December 24, 2005 at 6:38 am

  #92535

  Hi

  (columns (varchar2 (30))

  I wish to have a query that picks the value having the longest match starting digit(s) with my input.

  Example

  In put ‘5544’

  ColumnA

  5500

  554

  5540

  5539

  The query will return ‘554’ as it got longest match starting digits with input.

  Will not pick ‘5540’ as matching end at last digit ‘0’.

  So can I any one play the magic for me.

  Wishes

  Jawad

• iBuildDW

  Mr or Mrs. 500

  Points: 536

  More actions

  December 24, 2005 at 7:36 am

  #611385

  Would somethink like this work?

  declare @val nvarchar(30)

  set @val = '5540' --The Value we're looking for

  declare @len smallint

  set @len = len(@val) -- The length of the value

  while (@len > 0) -- Loop through variable

  begin

  if exists (select * from Table1 where left(@val,@len)=ColumnA) begin --Is there a match?

  select * from Table1 where left(@val,@len)=ColumnA

  break --We found a match, don't look any more

  end

  set @len=@len-1 --make the vaue smaller

  end

  Let me know if you have any questions..

  Cheers

• Jawad Ahmed Siddiqui

  Old Hand

  Points: 301

  More actions

  December 26, 2005 at 1:13 am

  #611409

  Thanks Dear it is just what I want.

• Jawad Ahmed Siddiqui

  Old Hand

  Points: 301

  More actions

  December 26, 2005 at 10:08 pm

  #611485

  One more thing please now I simply want to automate it for a long list instead of a single value.

   

  Details

  In above example I have to pass every value manually i.e. 5540 is there anyway through which I can do following.

   

  Take all values one by one from a table (Table2.Col1).

  Compare it with Table1.ColumnA (Just as in above example)

  Pass the result in Table2.Col2

   

  Wishes

  Jawad