Extracting data from a SELECT LIKE

Question

How guys,

I'm having a problem to display data from a field that contains many names separated for a semicolon. My client wants a display with just names that match the search criteria, like the below sample:

   Fields
   Domain                    Names                 
   ---------------------------------------------------------
   England                   Alice;Bob;Carl;David;Elton

What I need is, using the following Query:

   select * from database where Names like 'a%'

get

   England                   Alice;Carl;David

or even having

   select * from database where Names like 'Da%'

get

   England                   David

and also, using

   select * from database where Names like '%'

get

   England                   Alice;Bob;Carl;David;Elton

I'm working on MS-SQL 2012 and I have a StoredProcedure to get the selected rows but everytime I get the result like the above/last line: all names.

How can I hilite/display just the located string by the entried criteria?

Thank you very much for any help.

Why would you expect/want a search on `Da` to return `England`? Off the top of my head, look into using full text search. — Tim Biegeleisen, Dec 01 '17 at 01:37
My question is NOT related to return England but to get ONLY the fullname I had utilized in the search criteria. — David BS, Dec 01 '17 at 01:44
You have an answer below which looks promising, but I would argue that the best thing to do would be to normalize your data, and move those semicolon separated names onto separate records. Dealing with such data will be a real headache, as you have already seen. — Tim Biegeleisen, Dec 01 '17 at 01:56
Yes @Tim, the problem I have is because the database is of a client with almost 100,000 records and supported by several applications. We need to develop a new one, considering that we cannot create the structure like you recommend. I big headache really. — David BS, Dec 01 '17 at 02:22

score 0 · Accepted Answer · answered Dec 01 '17 at 01:48

The first thing to do with those names is to "split" then into individual rows THEN you can search for them individually. There are a myriad "split string" functions for T-SQL around so I'm not going to offer a treatise of which one to use. e.g. Split strings the right way – or the next best way (Aaron Bertrand)

Below is one way to do it that is OK for small(ish) requirements:

Demo at SQL Fiddle

CREATE TABLE Table1 ([Domain] varchar(7), [Names] varchar(26)) ;

INSERT INTO Table1 ([Domain], [Names])
VALUES ('England', 'Alice;Bob;Carl;David;Elton') ;

Query:

select
    t.Domain
  , split.value('.', 'varchar(50)') as [Name]
from Table1 t
cross apply (
    select cast('<r>'+replace(Names, ';', '</r><r>')+'</r>' as xml)
    ) as ca1(split1)
cross apply split1.nodes('r') as ca2 (split)

Results:

|  Domain |  Name |
|---------|-------|
| England | Alice |
| England |   Bob |
| England |  Carl |
| England | David |
| England | Elton |

Once you have the rows in this manner it is simple to find the ones that match the criteria. Once you have done that then (if wanted) it is possble to once again combine the names back into a separated string using "for xml path" (and there are many examples of how to use that too). see How Stuff and 'For Xml Path' work in Sql Server

Extracting data from a SELECT LIKE

1 Answers1