Like statement pulling too much data

keeps pulling 10, 11, 12 etc is there a way to limit this to just 1, 

if you mean just to return 1 record for such similarity, then just add the TOP clause into your select query, such as:

Select Top 1 from yourTable cv where cv.STR_VALUE Like '%1,%'

Select allOpen in new window

No let me restate the field is multiple entries 1,2,3--10,11,12  I need to pull just 1, the statement I have pulls 1, 10, 11,

sorry that's not so clear to me.

can you provide the input and output value? is it to pulling data or format the values at field level?

CREATE TABLE TableOne
    ([productIDs] varchar(13))
;


INSERT INTO TableOne
    ([productIDs])
VALUES
    ('1,10,3'),
    ('4,11,2'),
    ('5,8,1')
;

Select allOpen in new window

 

SELECT
  productIDs


FROM
  TableOne
 
  WHERE
   
  '1' IN (SELECT value FROM STRING_SPLIT(productIDs, ','));  

Select allOpen in new window

|------------|
|     1,10,3 |
|      5,8,1 |
       

Select allOpen in new window

https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15

try this.

','+cv.str_value+',' like '%,1,%'

Select allOpen in new window

here is an example:

create table t(STR_VALUE varchar(20))
insert into t values ('1,2,3'),('10,11,12'),('4,3,1')
select * from t where ','+str_value+',' like '%,1,%'
/*
1,2,3
4,3,1
*/

Select allOpen in new window

I believe the question is asking how to ignore numbers other than 1

I.e. how to ignore 10 or to ignore 111 or ignore 12 etc.

It would be great if the question could be clarified and some sample data provided.

SELECT * FROM table WHERE LEFT(column,1) = '1'

or

DECLARE @value NVarChar(200) = '1,2,3--10,11,12'
SELECT * FROM string_split(@value,',') WHERE value = '1'

IF Exists(SELECT * FROM string_split(@value,',') WHERE value = '1') PRINT '1 exists'
ELSE PRINT '1 does not exists'

(((cv.STR_VALUE)) Like ('%1,%')
(((cv.STR_VALUE)) Like ('%1,')
 

 

Does that work if the data stored looks like ('5,8,1')

In other words, the '1' is the last item and does not have a comma at the end?