-1

which is the best way to separate a string separated by comma and take each single value? example

@variable = john,emma,walter, ... , dallas,

then take the single value like...

john
emma
walter
...
dallas

I need each value to dynamically create a select statement like:

select * from tableName where john = ... and emma = ... //etc...

Thank you all

Solution How to split a comma-separated value to columns is different because it spit into column... i need to use each value for a select/where statement...

Andrea
  • 11,801
  • 17
  • 65
  • 72
Robertuzzo
  • 139
  • 1
  • 2
  • 7
  • 1
    use `string_split()` function – Ed Bangga Oct 09 '19 at 08:07
  • 1
    Which version of the SQL Server? – gotqn Oct 09 '19 at 08:08
  • Wait, you're trying to check if the *column* `john` has a value of *something* and the column `emma` has the value *something*, etc, not if a column has the value `'John'`? – Thom A Oct 09 '19 at 08:13
  • 2
    Possible duplicate of [How to split a comma-separated value to columns](https://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns) – Ilyes Oct 09 '19 at 08:13
  • What does `last` version means? SQL Server 2017 or SQL Server 2019 RC? And what will be the last version when some open your question after few years? – gotqn Oct 09 '19 at 08:16
  • Possible duplicate of [Turning a Comma Separated string into individual rows](https://stackoverflow.com/q/5493510/3484879), ***if*** OP actually wants `[Column] = 'John'` and not `[John] = {some value}`. – Thom A Oct 09 '19 at 08:18
  • for Larnu, the name are all primary key of a table, so i need it to create the where condition in the select statement... after the = are the value – Robertuzzo Oct 09 '19 at 08:19
  • 1
    Sample data and expected results would really help here, as I really don't understand what you mean by `where john =`. Where is the column `John` coming from? If it's coming from the delimited data, where is the **value** for `John` coming from? You delimited list isn't a CSV. – Thom A Oct 09 '19 at 08:19
  • So you have the primary key which is made up of the ***columns*** `John`, `emma`, `walter`, `dallas`, etc? That sounds like a huge design flaw. Normally a Primary Key is a single column, or at most 2, which as a combination make a composite primary key. For example, `OrderID` and `StockID`, which contains a column `Quantity` in a "joining" table for a many-to-many relationship. – Thom A Oct 09 '19 at 08:20
  • As @Larnu said, please edit your question to include sample data as _formatted text or DDL and DML or better create a fiddle_ and not **images**, and provide the expected results. As it is now we can't understand what's the issue and what 're trying to achieve. – Ilyes Oct 09 '19 at 08:22
  • SQL Server 2017 already has [STRING_SPLIT](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-2017) for this. You can loop over the returned values to generate the statement or you can explain what that statement actually does, since it's quite likely there are *better* options. – Panagiotis Kanavos Oct 09 '19 at 08:22
  • @PanagiotisKanavos - that's if he has access to 2017 or better... – Paul Oct 09 '19 at 12:44
  • @Paul the question says `SQL Server 2017` – Panagiotis Kanavos Oct 09 '19 at 12:50
  • @PanagiotisKanavos - Duh! Yep - missed that - not having the best day today! [rollseyes] – Paul Oct 09 '19 at 12:56
  • why for this downvote ? i didn't found a solution in google so i ask here, i didn't found another similar question here.... can you please take off the down vote? thank you – Robertuzzo Oct 25 '19 at 08:26

2 Answers2

1

Use variable table and join with it

Declare @SplitDate Table(
    DataValue NVARCHAR(100)
)
DECLARE @variable NVARCHAR(MAX)  = 'john,emma,walter,dallas'
INSERT INTO @SplitDate(DataValue)

SELECT value FROM STRING_SPLIT(@variable, ',');


SELECT * FROM @SplitDate
man shitan2
  • 46
  • 2
0

Turn your condition the other way round. What you should be searching for is...

WHERE 
    ',' + 'john,paul,ringo,george' + ',' LIKE '%,' + myfield + ',%'

Note that I appended the extra sets of speech marks so you could use a list from a variable, e.g.

WHERE 
    ',' + @mylist + ',' LIKE '%,' + myfield + ',%'
Paul
  • 4,160
  • 3
  • 30
  • 56