1

I want to find records using like query but in reverse mode

For exa: I have one string ts5e434 And now in databse I have one column called geohash and its contan comma seperated values

1) "ts5e4,ts5,ts5e434"

2) "ab,ye"

3) "ts,thh"

4) "t"

So here I want to get 1, 3 and 4 no records because its partially matching string

exa like clause

SELECT 
    *
FROM
    service_geohashes
WHERE
    'ts5e434' LIKE geohashes

Can anyone help me

Thanks in advance

Eyeslandic
  • 14,553
  • 13
  • 41
  • 54
ashvin
  • 2,020
  • 1
  • 16
  • 33

1 Answers1

1

I created function "LikeAny" in MSSQL which looks like:

CREATE  FUNCTION [dbo].[LikeAny](@text nvarchar(MAX), @delimiter varchar(20), @comparestring nvarchar(MAX))
RETURNS BIT AS
BEGIN 
    DECLARE @LikeAny BIT = 0,
            @TempString nvarchar(MAX)
    DECLARE MY_CURSOR CURSOR 
      LOCAL STATIC READ_ONLY FORWARD_ONLY
    FOR 
    SELECT value FROM STRING_SPLIT(@text, @delimiter) 
    OPEN MY_CURSOR
    FETCH NEXT FROM MY_CURSOR INTO @TempString
    WHILE @@FETCH_STATUS = 0
    BEGIN 
        --Do something with Id here
        IF (@TempString <> '' AND @comparestring LIKE N'%' + @TempString + '%')
        BEGIN
            SET @LikeAny = 1
            BREAK;
        END
        ELSE
            FETCH NEXT FROM MY_CURSOR INTO @TempString
    END
    CLOSE MY_CURSOR
    DEALLOCATE MY_CURSOR
    RETURN @LikeAny
END

If you use this in your example, it should look like:

SELECT 
    *
FROM
    service_geohashes
WHERE
    [dbo].[LikeAny](geohashes ,',', 'ts5e434') = 1

I tried also to convert the function above into MySQL but I had no option to test it on real environment it looks like:

DROP FUNCTION IF EXISTS LikeAnyCommaDelimited;
DELIMITER $$
CREATE FUNCTION LikeAnyCommaDelimited(p_text longtext, p_comparestring longtext)
RETURNS TINYINT
DETERMINISTIC
BEGIN 
DECLARE v_finished INTEGER DEFAULT 0;
DECLARE v_LikeAny TINYINT DEFAULT 0;
DECLARE v_TempString longtext;    
DECLARE v_SQL longtext;
drop temporary table if exists tempa;
drop temporary table if exists tempb;
CREATE TEMPORARY TABLE tempa( txt text );    
CREATE TEMPORARY TABLE tempb( val char(255)  );
insert into tempa values(p_text);
set v_SQL = concat("insert into tempb (val) values ('", replace(( select group_concat(distinct txt) as data from tempa), ',', "'),('"),"');");
prepare statement1 from @sql;
execute statement1;
DEClARE split_cursor CURSOR FOR 
SELECT value FROM (select distinct(val) as value from tempb);
DECLARE CONTINUE HANDLER 
FOR NOT FOUND SET v_finished = 1;
OPEN split_cursor;
get_string: LOOP
FETCH split_cursor INTO v_TempString;
IF v_finished = 1 THEN 
LEAVE get_string;
END IF;
IF (v_TempString <> '' AND p_comparestring LIKE N'%' + CONCAT(v_TempString , '%') THEN
BEGIN
    SET v_LikeAny = 1;
    LEAVE get_string;
END
END LOOP get_string;
CLOSE split_cursor;
END$$
DELIMITER ;

Let me know if you have any issues.

Irakli
  • 562
  • 4
  • 15