SQL Amazing Trick | Dynamically Pass Values to IN clause | Parametrize Values | String_Split

In this video tutorial, we discuss how to dynamically pass values to IN clause.
How to install SQL Server for practice?
• How to install SQL Ser...
Check out the complete list of SQL Query Interview Questions -
• SQL Query Interview Qu...
Best Data Science / Analytics / SQL courses
Learn SQL Basics for Data Science Specialization
imp.i384100.net/qnXYk5
Data Science Fundamentals with Python and SQL Specialization
imp.i384100.net/mgVYre
Python for Everybody Specialization
imp.i384100.net/DVz7Aj
Google Data Analytics Professional Certificate
imp.i384100.net/OR37oQ
Coursera Plus - Data Science Career Skills
imp.i384100.net/c/3299742/132...
Please do not forget to like, subscribe and share.
For enrolling and enquiries, please contact us at
Website - knowstar.org/
Facebook - / knowstartrainings
Linkedin - www.linkedin.com/company/know...
Email - [e-mail]

[@LearnatKnowstar]

Register at the below link to get US $100 off for Coursera Plus membership between Sep 8 - Sep 29, 2022.
The Coursera Plus membership gets you access to unlimited courses and unlimited certifications!
imp.i384100.net/Ke51on

[@higiniofuentes2551]

Thank you for this very useful video!

[@alexnevidimkins4730]

Important point - STRING_SPLIT requires the compatibility level to be at least 130. When the level is less than 130, SQL Server is unable to find the STRING_SPLIT function.

[@raginranga3494]

I would not change Level on Prod db without research

[@rakeshk9947]

Good information , But will suggest you to use trim function on value column , It will give more accurate information .

[@nbso08]

Tengo más de 20 años usando Sql Server, y nunca me dió por usar esta forma, la practicaré.
Saludos desde Tlajo Jal Mex.

[@LearnatKnowstar]

Thank you. Glad it was useful.

[@ravindrasahasrabudhe8484]

Great, this might be good for small query, the IN clause may create problem in bigger queries.
but as this function return table, need to find out whether it can be used for populating other temp table and then use with join in bigger queries, a traditional approach for dynamic values.

[@Belsteak]

then you use the string_split function in a join and the problem is solved.
With even bigger table, you create a table variable with a key and then you fill that table first with the string_split then use a join.
In 2019 normally sql will avoid the bug that considers only one row in the variable table linking to bad plan when joining. But for earlier versions the index/or key on the variable table will solve that problem.

[@jacksparrowcapitan3232]

Thanks for the video

[@s0nnyv123]

Great videos! Love the pace. Subscribed!

[@LearnatKnowstar]

Thank you for your support

[@anburenganathan8369]

Thanks for the video... I have one doubt i want to filter only red n black but the @var has (Red, Yellow, Grey, Black)...

[@higiniofuentes2551]

Do you have information about how to do this in a DB2?
Thank you!

[@junaidmalik9593]

You guys r awesome, the reason being ur videos are so different and usefull in real world.

[@LearnatKnowstar]

Thank you 🙏

[@HARSHAKNV95]

Could there be any complications that might occur if we change compatibility level?

[@sunrathod8641]

Hi mam how we can get output header and few records ..again header after few records and details records ?

[@higiniofuentes2551]

Where I can find information about the select [value] from....?
Thank you!

[@maheshk3891]

instead in Excel=CHAR(39)&A2&CHAR(39) then for that result =CONCATENATE(B2,$B$1) . Here B1 is , . Then drill down to all the values.

[@rutgerdekok1324]

If string_split isn't supported in your version of sql, like in mySQL, you can use:
WHERE FIND_IN_SET(color, @var) > 0;
or
WHERE CONCAT(',', @var, ',') LIKE CONCAT('%,', color, ',%');

[@katywalker8322]

You can do strings_split in MySQL using substring, doing a cross join to return a list of numbers
For example, something like this.
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(‘some,list,of,comma,separated,values’, ‘,’, aNum), ‘,’, 1)
FROM
( SELECT tens.acnt * 10 + units.acnt AS aNum
FROM
(SELECT 1 AS acnt UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units
CROSS JOIN
(SELECT 1 AS acnt UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens
) AllNums
This can be cleaned up to remove duplicates, or to cope with more than 100 comma separated values.
Only when desperate though! Possibly useful when trying to compare values in 2 comma separated lists (after you have given the database designer a piece of your mind). Probably better to use something like this to extract to a temp table which can then be indexed.

[@rohanthakur6136]

Great 👌

[@LearnatKnowstar]

Thank you!

[@kashmirshadows8150]

Thank you for the video

[@LearnatKnowstar]

Thank you

[@abdullahquhtani4247]

Great But what if I want to use int instead of varchar ?

[@visaalakshiselvaraj5572]

Very useful 🙏

[@LearnatKnowstar]

Thank you

[@dimpyrathore818]

Thank you

[@LearnatKnowstar]

Thank yoy

[@ArabianHorse]

The Sound (voice) is not so clear. Please use a good near by ( 20 cm max ) mic or smart phone.

[@vamshitha449]

Write a query to get first and last row of records from table

[@aniketjain3318]

select top 1 * from person.person
union all
Select * from
(select top 1 * from person.person
order by BusinessEntityID desc)x

[@vamshitha449]

@@aniketjain3318 is this for SQL server

[@vamshitha449]

As well as how to get particular row from a table in SQL server

[@aniketjain3318]

@@vamshitha449 Yes. For Oracle and MySQL, you can use Limit () function.

[@aniketjain3318]

@@vamshitha449 Solution 1: Identify the column or set of column having unique value so that you can use that value in your where clause to get the particular row from a table.
Solution 2: Use row_number() to assign unique value to each row of the table and then using CTE, you can retrieve that particular row.

[@mustaqahmedattar8462]

Table contains
emp_id, first_name, last_name
101. , Satish. , Kumar
102. , rupesh. ,
103. , , Chavan
104. , ,
Output should be
Emo_id, name, name type
101. , Satish, FN
101. , Kumar, LN
102. , rupesh, FN
103. , Chavan, LN
QUERY: record count of output

[@madhavanrao3626]

Please improve your audio quality

[@meerasahebattar4715]

How to find name start with 'A' and 3rd character is not 'E'

[@aniketjain3318]

Select * from person.Person where FirstName like 'A_[^E]%'

[@cockbeard]

@@aniketjain3318 regex???? No thanks

[@cockbeard]

WHERE CHARINDEX('A',ColName)=1
AND SUBSTRING(ColName,3,1) 'E'
Be wary of case sensitivity, add an OR if needed within brackets, I wouldn't use NOT IN to save a line of code because NULL values will give false positives

[@aleksastefanovic9615]

Windows 11 update is waiting for you :)

[@georgelovell477]

If your expectations are to reach US/Canadian/British audiences this delivery failed. Slow down your speech delivery. Content is not issue but it’s delivery.

[@sunrathod8641]

Hi mam how we can get output header and few records ..again header after few records and details records ?