CODEWITHSUNDEEP
t-sqlsubstring
Yan Kleber
Yan Kleber

Reputation: 405

Substringing results with % wildcard in T-SQL?

I need to extract a substring from a select result which search was made from part of the same string!

I need to perform a search with % wildcard. My problem is that instead to return the whole column I need to return only the specific contents into the column that matches the query search

For instance, suppose I have the following table:

ID Description
1  Yellow shirt 12345A
2  Red shirt 12345AB
3  Shirt blue 12345ABC
4  Shirt bluee 12345ABCD
3  Shorts small Product 12388X
4  Long shorts 12388XY

Then the following query:

DECLARE @mystring as varchar(10)
SELECT description from mytable where description like '%' + @mystring + '%'

Will return me:

Yellow shirt 12345A
Red shirt 12345AB
Shirt blue 12345ABC
Shirt bluee 12345ABCD

However I would like that it returns me this:

12345A
12345AB
12345ABC
12345ABCD

How should I filter it in order to achieve such result?

Thanks!

Upvotes: 0

Views: 589

Answers (2)

Alan Burstein
Alan Burstein

Reputation: 7918

Building on Anthony's excellent solution, you can accomplish this with CHARINDEX which will be cleaner and faster.

SELECT
    t.*,
    DerivedColumn = SUBSTRING(TestValue,CHARINDEX(@myString, TestValue),LEN(TestValue))
FROM  #Test AS t
WHERE CHARINDEX(@myString, TestValue) > 0;

You can use APPLY for even cleaner code like this:

SELECT
    t.*,
    DerivedColumn = SUBSTRING(TestValue,f.Pos,LEN(TestValue))
FROM        #Test AS t
CROSS APPLY (VALUES(CHARINDEX(@myString, TestValue))) AS f(Pos)
WHERE       f.Pos > 0;

Upvotes: 3

Anthony Hancock
Anthony Hancock

Reputation: 931

If I understand your question correctly, what you are looking for is PATINDEX.

DROP TABLE IF EXISTS #Test;

CREATE TABLE #Test
(
    TestID TINYINT PRIMARY KEY
    ,TestValue VARCHAR(100)
)
;
GO

DECLARE @myString VARCHAR(10) = '12345A';

INSERT INTO #Test
VALUES
(1,  'Yellow shirt 12345A')
,(2,  'Red shirt 12345AB')
,(3,  'Shirt blue 12345ABC')
,(4,  'Shirt bluee 12345ABCD')
,(5,  'Shorts small Product 12388X')
,(6,  'Long shorts 12388XY')
;

SELECT
    *
    ,DerivedColumn = SUBSTRING(TestValue,PATINDEX(CONCAT('%',@myString,'%'),TestValue),LEN(TestValue))
FROM #Test
WHERE PATINDEX(CONCAT('%',@myString,'%'),TestValue) <> 0
;

Upvotes: 2

Related Questions