CODEWITHSUNDEEP
sqlselectsql-likexquery-sqlcontainstable
DS Reveles
DS Reveles

Reputation: 1

SQL - match part of a string with Column value

The user input the following string 2SMD4COMPUTEREH2KLD [free input] what i need is to find the correct id in the table devices

The user input the following string 2SMD4COMPUTEREH2KLD [ 2SMD4COMPUTEREH2KLD ] what i need is to find the correct id in the table in this case Id = 3, in the next iteration the user could input KL2USB3UYDJKWLDK [USB IS INSIDE THE STRING], in this case the id should be = 2

ID Value
1 LAPTOP
2 USB
3 COMPUTER
4 MONITOR
5 MOUSE

Upvotes: 0

Views: 57

Answers (3)

Saikat
Saikat

Reputation: 110

How about this for a start? Pass the input which has been declared in the first line in your query. The query will run in the SQL server. Basically, you can use like keyword to match the pattern. Whichever is matched will get returned. Instead of concat function, you can use + operator to concat.

declare @value varchar(40) = '2SMD4COMPUTEREH2KLD';

with cte AS
(
    select 1 as id , 'LAPTOP' as gadget
    union ALL
    select 2 as id , 'USB' as gadget
    union ALL
    select 3 as id , 'COMPUTER' as gadget
    UNION ALL
    select 4 as id , 'MONITOR' as gadget
    UNION ALL
    select 5 as id , 'MOUSE' as gadget
 )
 SELECT id , gadget from cte , (select @value as value) as b
 where value like concat('%',gadget,'%');

Upvotes: 0

QuestionGuyBob
QuestionGuyBob

Reputation: 313

Here is how I would accomplish your request using SQL Server SSMS as an example:

DROP TABLE IF EXISTS #TEST_DATA;

CREATE TABLE #TEST_DATA(
    ID INT IDENTITY(1,1)
    ,[VALUE] VARCHAR(20)
)

INSERT INTO #TEST_DATA (
    VALUE
)
VALUES
('LAPTOP'), ('USB'), ('COMPUTER'),('MONITOR'), ('MOUSE')

DROP TABLE IF EXISTS #TEST_VALUES;

CREATE TABLE #TEST_VALUES(
    [INPUT] VARCHAR(20)
)

INSERT #TEST_VALUES (
    INPUT
)
VALUES
('2SMD4COMPUTEREH2KLD'), ('KL2USB3UYDJKWLDK')


SELECT
    TD.*
    ,TV.INPUT
FROM
    #TEST_VALUES TV
LEFT OUTER JOIN
    #TEST_DATA TD
    ON TV.INPUT LIKE '%' + TD.VALUE + '%'

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521467

You could use a dynamic LIKE expression:

SELECT ID
FROM yourTable
WHERE <input> LIKE '%' || Value || '%';

Using the example you gave, the following would be true:

2SMD4COMPUTEREH2KLD LIKE '%COMPUTER%'

and would return ID = 2.

Upvotes: 0

Related Questions