0

Suppose 'allotedTo' column contains multiple values like 'ABC/xyz/RAM'
How do I load all rows, with 'allotedto' column containing any one of given input values? (eg: all rows alloted to RAM or KLM) ?

Here, we don’t want the solution to repeat column name multiple time like below:

select * from table where [allotedTO] like '%RAM%'  OR [allotedTO] like '%KLM%'

We want to implement solution similar to below queries, but it is not working :

select * from table where [allotedTO] like '%RAM|KLM%' 


SELECT PATINDEX('%[RAM|KLM]%', [allotedTO]) 
3
  • @Akina Pls check updated question Jul 24, 2019 at 10:49
  • Would I be guessing if I were to assume that you actually want to input the search values somewhere and allow them to be assigned to a variable in an SQL Statement which then selects the data?
    – John K. N.
    Jul 24, 2019 at 10:56
  • @hot2use you are correct , same purpose Jul 24, 2019 at 11:10

4 Answers 4

2

For the specific example:

WHERE [allotetTO] LIKE '%RA[MJ]%'

For more complex ones, you could use a join to a values table with the wanted patterns:

1
  • Pls check updated question Jul 24, 2019 at 10:53
2

I did a quick google, and sql-server's support for regular expressions seems rather limited. For trivial patterns like [ABC|CDE] you can use a cte or sub-select to join against:

with T (allottedTo) as ( 
    select 'ABC/xyz/RAM' 
    union 
    select 'AKK/vyz/KRA' 
    union select 'KLM/uyh/RAJ' 
) 
select distinct T.allottedTo 
from T 
JOIN ( VALUES ('RAM'), ('RAJ' ) ) AS X (pat) 
    ON T.allottedTo LIKE CONCAT('%', X.pat, '%');
5
  • 1
    Maybe a separator should be a part of the search pattern to avoid nested values Jul 24, 2019 at 10:35
  • I'm not sure what you mean, can you add an example? Jul 24, 2019 at 10:38
  • For instance value 'ABC/xyz/RAMAM' will meet the pattern %RAM%, but won't meet '%/RAM/%'. Jul 24, 2019 at 10:44
  • 1
    I see, on the other hand 'RAM/aaa/BBB' wont meet that pattern either. If allottedTo would start and stop with the separator token, adding the token to the pattern would be perfect. Jul 24, 2019 at 10:52
  • Nothing can stop us from adding the separator to the start and to the end of allottedTo, anyway the condition is NON-SARGABLE ON '/' + RTRIM(allottedTo) + '/' LIKE CONCAT('%/', X.pat, '/%') Jul 24, 2019 at 11:53
1

If you are using SQL Server 2016 or above and you want to use a string of separated values as input, you can use the table valued function STRING_SPLIT(). This function splits a string into rows based on a specified separator and returns the data in a table.

For example:

DECLARE @LookupTable TABLE (id INT IDENTITY(1,1), allottedTo NVARCHAR(150))
DECLARE @ValuesToSearch TABLE (allottedTo NVARCHAR(150))
DECLARE @StringToSearch NVARCHAR(1000)

INSERT INTO @LookupTable(allottedTo)
VALUES('ABC/xyz/RAM')
, ('AKK/vyz/KR')
, ('KLM/uyh/RAJ')

SET @StringToSearch = N'RAM|KLM'

INSERT INTO @ValuesToSearch
SELECT [value]
FROM STRING_SPLIT (@StringToSearch, '|')

SELECT lt.*
FROM @LookupTable lt
    INNER JOIN @ValuesToSearch vts ON lt.allottedTo LIKE '%'+vts.allottedTo+'%'

SELECT PATINDEX('%'+vts.allottedTo+'%', lt.allottedTo) 
FROM @LookupTable lt
    INNER JOIN @ValuesToSearch vts ON lt.allottedTo LIKE '%'+vts.allottedTo+'%'
1

Suppose 'allotedTo' column contains multiple values like 'ABC/xyz/RAM'

This breaks the basic data atomicity rule that are part of Data Normalisation. One field, one value.

You should spin this out into a separate table, linking the parent record's "id" and each of the alloted values:

select * from allocations ; 

+----+---------+
| id | alloted | 
+----+---------+
|  1 | ABC     |
|  1 | xyz     | 
|  1 | RAM     |
|  2 | KLM     |
|  2 | RAM     |
+----+---------+

Then, to find the id's of records with [at least one of] the alloted values:

select id 
from table 
where alloted in ( 'RAM', 'KLM' )
group by id 

+----+
| id |
+----+
|  1 |
|  2 |
+----+

Or, to find rows that have a given number of the alloted values:

select id 
from table 
where alloted in ( 'RAM', 'KLM' )
group by id 
having count( * ) = 2 

+----+
| id |
+----+
|  2 |
+----+

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.