selecting the nearest match with LIKE or what

[rtdvoip]

Hiya fellas,

Is there a thread out there giving any hint or information on string compares.

I need to find the nearest match on some numbers and using it do one other computation.

for example : breakno = 1234
next breakno = 12346
using an index (known) and having a number like '12345' for comparison to the breakno.
If 12345 does not exist and it checks 12346 but not equal,
1234 being the nearest I need to return the assocaited field. Any thoughts? Could LIKE be used for this or do I need to go more in depth?

Thanks in advance,
rtdvoip

 

[LogicalmanUS]

RTDVOIP,

To find an actual integer match, or the next highest is quite simple.
Remember that Integers do not use quotes, but all strings do.

Using your code above, and searching for the number 12344 or the next highest, can be achieved using:

SELECT TOP 1 breakno WHERE breakno > (12344-1)

For the next lowest number use:

SELECT TOP 1 breakno WHERE breakno < (12344+1) order by breakno DESC

The above two are only good for Numerical Data.
For String data you will need to first check the whole string, and then loop through a process of substrings.

I was not certain exactly what data type the column breakno was from your question though.

With further info, I'm sure an answer can be found.

Logicalman

 

[rtdvoip]

LogicalmanUS

the breakout numbers are in a string.
what you proposed doing with the substring is exactly what i have been working on.

current state:

select price
from table
where (breakout_no = prefix and index = rp.index)
or while @i < len(breakout_no)
begin
if substring(breakout_no, 1,@x)=prefix
@x=@x+1
@i=@i+1
else
begin @x=@x-1
select price
from table
where substring(breakout_no, 1,@x) = prefix and index =rp.indx.

just a rough first draft. any suggestion?

thanks a ton.
rtdvoip

 

[clarkin]

its a pretty complicated idea - you want a sort of fuzzy match?

My advice is to think up an algorithm that will do it for you and then code it. (Changing it to code is the easy part)

So you could go for ..
1. check exact match
2. check +10 and -10 if integer
3. check +3 and -3 in alphabet (last letter) if not integer
4. truncate last character & repeat (maybe max 2 times)

Good luck

Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].

 

[LogicalmanUS]

rtdvoip,

Here's the full SProc you need.
It does all the different searches you need, read the remarks for each part, and you can decide on what type of search/result matches your needs.

Hope it helps

Logicalman

Copy and paste all the code below into a SProc, change the table/column names as required, and run it from a Query Analyzer, using a string parameter:
EXEC sp_rtdvoip_1 '12336'

The output will be both in the grid and messages areas.


CREATE PROCEDURE [dbo].[sp_rtdvoip_1]

@prefix varchar(100) = ''

AS

-- Stop irrevelent outputs
SET NOCOUNT ON

-- Get rid of any spaces
SET @prefix = RTRIM(LTRIM(@prefix))

-- Declare variables to use
DECLARE @x int
DECLARE @Price money
DECLARE @LENGTH int
DECLARE @prefix1 varchar(100)
DECLARE @Match varchar (100)

-- Test for supplied value
SET @Price = (select price from table1 where breakout_no LIKE @prefix )
-- Was it found? If so, then print it and skip the rest of the code
IF NOT (@Price=NULL)
BEGIN
PRINT 'FOUND!!'
PRINT 'PRICE: ' + cast(@Price as varchar(100)) + ' for breakout_no: ' + CAST(@prefix AS varchar(100))
GOTO end_sp
END

-- Oops, not found, so we go searching
PRINT 'SEARCHING ...'

-- Lets get a temp table, and convert all the strings into numerical data
CREATE TABLE #table1
(
price money,
breakout_no int
)
INSERT #table1
SELECT price, CAST(breakout_no AS int) FROM table1 order by CAST(breakout_no AS int)

-- SELECTING FROM ORIGINALTABLE
-- Search for the next lowest Numerical Value
SELECT TOP 1'Next Highest Numerical Value' AS TYPE, price, breakout_no FROM table1
WHERE CAST(breakout_no as int) > (CAST(@prefix as int)-1)

-- Search for the next Highest Numerical Value
SELECT TOP 1 'Next Lowest Numerical Value' AS TYPE, price, breakout_no FROM table1
WHERE CAST(breakout_no as int) < (CAST(@prefix as int)+1)
order by breakout_no DESC

-- Total table output
SELECT 'All Values' AS TYPE, price, breakout_no FROM table1
order by CAST(breakout_no AS int)

-- SELECTING FROM TEMP TABLE
-- Search for the next lowest Numerical Value
SELECT TOP 1'Next Highest Numerical Value' AS TYPE, price, breakout_no FROM #table1
WHERE CAST(breakout_no as int) > (CAST(@prefix as int)-1)

-- Search for the next Highest Numerical Value
SELECT TOP 1 'Next Lowest Numerical Value' AS TYPE, price, breakout_no FROM #table1
WHERE CAST(breakout_no as int) < (CAST(@prefix as int)+1)
order by breakout_no DESC

-- Total table output
SELECT 'All Values' AS TYPE, price, breakout_no FROM #table1
order by CAST(breakout_no AS int)

-- Lets get the length of the parameter
-- so we can reduce it's size for matching loop below
SET @LENGTH = LEN(@prefix)

-- Debug Prints
PRINT'@prefix : ' + CAST(@prefix AS varchar(100))
PRINT'LENGTH : ' + CAST(@LENGTH AS varchar(100))

-- Set the loop counter
SET @x = @LENGTH
-- Keep looping until it's zero
WHILE(@x>0)
BEGIN
-- Get the new string
SET @prefix1 = LEFT(@prefix, @x)
-- Check for match
SET @Price = (select price from table1 where breakout_no LIKE @prefix1 )
-- Test if match has been found
-- If so, then get out of the loop
IF NOT (@Price=NULL)
BEGIN
SET @Match = (SELECT TOP 1 breakout_no FROM table1 WHERE price = @Price)
PRINT 'FOUND!!'
PRINT 'PRICE: ' + cast(@Price as varchar(100)) + ' breakout_no:' + CAST(@Match AS varchar(100))
GOTO end_sp
END

PRINT'@prefix : ' + CAST(@prefix1 AS varchar(100))
SET @x = @x - 1
SET @prefix1 = LEFT(@prefix, @x)
END
-- Loop has ended
-- So has sp, so dispose of the temp objects
DROP TABLE #table1

end_sp:

/*

or while @i < len(breakout_no)
begin
if substring(breakout_no, 1,@x)=prefix
@x=@x+1
@i=@i+1
else
begin @x=@x-1
select price
from table1
where substring(breakout_no, 1,@x) = prefix and index =rp.indx.


*/
SET NOCOUNT OFF
GO

 

[rtdvoip]

Thanks to everyone for the help and hints and code.

rtdvoip