1

i have a stored procedure that searches like bellow:

BEGIN
#At first, Search in name of job:
(SELECT * FROM tb_job WHERE `name` LIKE '%some%' AND `name` LIKE '%thing%')
UNION 
# second, search for tags:
(SELECT * FROM tb_job WHERE id IN 
    (
        SELECT idJob FROM
        (
            (SELECT 2 AS priority1, COUNT(tb_job_tag.idTag) AS priority2, idJob FROM tb_job_tag WHERE idTag IN
                (SELECT tb_tag.id FROM tb_tag WHERE tag LIKE '%some%' OR tag LIKE '%thing%')
            GROUP BY tb_job_tag.idJob)
        UNION
            (SELECT 1, COUNT(tb_job_tag.idTag), idJob FROM tb_job_tag WHERE idTag IN
                (SELECT tb_tag.id FROM tb_tag WHERE tag LIKE '%some%' AND tag LIKE '%thing%')
            GROUP BY tb_job_tag.idJob)
        )
        AS t ORDER BY priority1, priority2 DESC
    )
)

END

now i have 2 questions: how can i pass an array of words and separate them in mysql and use them in LIKE? second, how can i make this search better?

(i have 3table: tb_job, tb_tag, tb_job_tag that stores job's id and tag's id). thanks for your help.

Fatemeh Gharri
  • 369
  • 2
  • 6
  • 20

1 Answers1

0
/**
 * http://www.aspdotnet-suresh.com/2013/07/sql-server-split-function-example-in.html
 */
CREATE FUNCTION dbo.Array(@String nvarchar(4000), @Delimiter char(1))
RETURNS @Results TABLE ([id] [bigint] IDENTITY(1,1) NOT NULL, Items nvarchar(4000))
AS
BEGIN
    DECLARE @INDEX INT
    DECLARE @SLICE nvarchar(4000)
    -- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z
    --     ERO FIRST TIME IN LOOP
    SELECT @INDEX = 1
    WHILE @INDEX !=0
    BEGIN
    -- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
    SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
    -- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
    IF @INDEX !=0
    SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
    ELSE
    SELECT @SLICE = @STRING
    -- PUT THE ITEM INTO THE RESULTS SET
    INSERT INTO @Results(Items) VALUES(@SLICE)
    -- CHOP THE ITEM REMOVED OFF THE MAIN STRING
    SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
    -- BREAK OUT IF WE ARE DONE
    IF LEN(@STRING) = 0 BREAK
    END
RETURN
END

execute this function once in your database and now onward to access specific value you can write your query like below

 DECLARE @VALUE VARCHAR(100);
 SELECT TOP 1 @VALUE = Items FROM [dbo].[Array] ('some,thing,like,that' , ',') where id = 2
 PRINT @VALUE

All you need to change is id in select statement. It'll accept only String values for now. But you can convert String to Int in SQL using CAST

I just created this function in hurry if you have any suggestions/modifications let me know...

Vicky Thakor
  • 3,847
  • 7
  • 42
  • 67