问搜索字符串并有条件地忽略连字符

EN

DECLARE @t TABLE (id int, keyword VARCHAR(8000));
INSERT INTO @t VALUES
(1, 'usedredcars'),
(2, 'used-redcars'),
(3, 'usedred-cars'),
(4, 'used-red-cars'),
(5, 'florida-usedredcars'),
(6, 'usedredcars-florida'),
(7, 'florida-usedredcars-florida'),
(8, 'rarelyused-red-cars'),
(9, 'used-red-carshops');

WITH cte AS (
    -- pad the seed string with hyphens to simplify edge case handling
    SELECT id, keyword, '-' + keyword + '-' AS search
    FROM @t AS base

    UNION ALL

    -- replace hyphens from the middle one at a time
    SELECT id, keyword, STUFF(search, CHARINDEX('-', search, 2), 1, '')
    FROM cte AS prev
    WHERE NULLIF(CHARINDEX('-', search, 2), LEN(search)) > 0

    -- BE ADVISED that STUFF can return unexpected size varchar and thus:
    -- Types don't match between the anchor and the recursive part
    -- explicitly cast the result to desired size if that happens
)
SELECT *
FROM cte
WHERE search LIKE '%-usedredcars-%'

  SELECT
    *
  FROM
    [table]
  WHERE
        REPLACE('-', '', description) LIKE keyword + '%'
    AND description LIKE REPLACE(keyword, '%-', REPLACE('-', '', description))

CREATE OR REPLACE FUNCTION keyword2re
(
  p_kw IN VARCHAR2
)
RETURN VARCHAR2
AS
  v_result VARCHAR2(1000);
  v_length INTEGER;
  v_count  INTEGER;
BEGIN

  v_length := LENGTH(p_kw);
  FOR v_count IN 1..v_length LOOP
    v_result := v_result || '[\-]*' || SUBSTR(p_kw, v_count, 1);
  END LOOP;
  v_result := v_result || '([\-].*)?$';
  RETURN v_result;

END keyword2re;

used

[\-]*u[\-]*s[\-]*e[\-]*d([\-].*)?$

SELECT *
FROM   my_table
WHERE  REGEXP_LIKE(description, keyword2re('fullstack'));