Thank you for the excellent explanation. I have a further complication with a concatenation string which is delimited by a series of pipes and parenthesis. With the value between the first pipe and parenthesis of varying length. The parenthesis is fixed and the distance between the close parenthesis and last pipe is also fixed.
i have 3 tables 01) table employee (columns are empid,empname , depid) 02) table Department ( columns are depid, dep name) 03) table salary (columns are empid,amount,month). Q) get the empname , how don't get the salary in the month of December please let me know the concept for this questions , how to solve
You can filter table 3 records for month - December. Th n do a left join between table 1 and filtered table 3 records. The records which do not exist in table 3 are the employees who did not get salary in December. We will post a video with solution soon.
1. Select value from string_split(string/column, delimiter) 2. Select value from table CROSS APPLY string_split(string/column, delimiter) 3. Select [1] AS .., [2] AS .., .. From CTE_Table PIVOT ( Function .. For column in ([1], [2],.. ) ) AS PivotTable
Hello! I'm having issue with this since my Table has a Value column name and is there a way to cast a column name for the CROSS APPLY SPLIT_STRING() to different name?
How could I transform a single column, where each row contains a string with 6 values separated by a comma, into 6 columns? Existing table: Unique column value1, value2, value3, value4, value5, value6 value A, value B, value C, value D, value E, value F ...
Can this be saved as a View for regular use? When I paste it into a view I get a syntax error "SQL text cannot be represented in the grid pane and diagram pane."
string split function was introduced in SQL 2017, but I want to do it in SQL 2014, how can I do that? I want to split values on specific characters because my column have multiple values
Why you have used this approach ? When we can solve it by substring & charIndex function only Select substring(name,1charindex(‘,’,name)-1) lastname, substring(name,charIndex(‘,’,name)+1,len(name)) from table
i have a column with a mix-up of 2 delimiters i.e. (;) and (|) and are not uniform in length, e.g 1 row has I=2551;A=5075;D=20120815 2 row has I=2166;A=4407.64;D=20120531|I=2500;A=90192.36;D=20120801 and this can be longer as per the allocations but each main part is separated by (|) i want to extract parts namely I=character. how do i do it since they are not uniform?
As you have a variable table, your problem statement is a bit complex for split string or any other SQL function. But you can search for Python data frame for this.
One of the (if not the most) most underrated SQL videos I have ever seen.
Glad that you found it useful.
After searching for many hours, I found a teacher. Thanks
Thank you so much!
Perfect! This is exactly what I'm looking for - cross apply split function to get the F/L name in row. Thank you!
Thank you
Thank you for the excellent explanation. I have a further complication with a concatenation string which is delimited by a series of pipes and parenthesis. With the value between the first pipe and parenthesis of varying length. The parenthesis is fixed and the distance between the close parenthesis and last pipe is also fixed.
Thank u very much, Plz put more videos like this, Really very helpful.
Thanks so much!
appreciate your way of teaching, really simple to understand.
Keep up the good work
Thank you
This is really useful.
Wow es el metodo más rápido que he encontrado, es muy veloz y no consume recursos. Gracias!!!
Thank you
nicely explained .. but row_number is an analytical function not an aggregate function
I have got what I was looking for .. Thanks... 😎
Thank you for sharing this.
Thank you
Good collection of sql functions
Thank you
Thank you for this awesome lesson!
Glad it was helpful.
i have 3 tables
01) table employee (columns are empid,empname , depid)
02) table Department ( columns are depid, dep name)
03) table salary (columns are empid,amount,month).
Q) get the empname , how don't get the salary in the month of December
please let me know the concept for this questions , how to solve
You can filter table 3 records for month - December. Th n do a left join between table 1 and filtered table 3 records. The records which do not exist in table 3 are the employees who did not get salary in December. We will post a video with solution soon.
1. Select value from string_split(string/column, delimiter)
2. Select value from table CROSS APPLY string_split(string/column, delimiter)
3. Select [1] AS .., [2] AS .., .. From CTE_Table
PIVOT (
Function ..
For column in ([1], [2],.. )
) AS PivotTable
what if we have 4 strings to extract? what is the best function to use? I am using SQL Server 2012 and we don't have the split function.
What if the name contains more than 3 name? Please reply, i need it
Is there any another method to do this ?
Hello! I'm having issue with this since my Table has a Value column name and is there a way to cast a column name for the CROSS APPLY SPLIT_STRING() to different name?
How could I transform a single column, where each row contains a string with 6 values separated by a comma, into 6 columns?
Existing table:
Unique column
value1, value2, value3, value4, value5, value6
value A, value B, value C, value D, value E, value F
...
You can try the same approach. It will work for multiple columns.
Can this be saved as a View for regular use? When I paste it into a view I get a syntax error "SQL text cannot be represented in the grid pane and diagram pane."
string split function was introduced in SQL 2017, but I want to do it in SQL 2014, how can I do that? I want to split values on specific characters because my column have multiple values
Why you have used this approach ? When we can solve it by substring & charIndex function only
Select substring(name,1charindex(‘,’,name)-1) lastname, substring(name,charIndex(‘,’,name)+1,len(name)) from table
I want to use delimiter as line feed
You can use Char(10) as the delimiter. Char(10) means line feed.
@@LearnatKnowstar thank you for the reply ! Had found it thanks
i have a column with a mix-up of 2 delimiters i.e. (;) and (|) and are not uniform in length, e.g
1 row has I=2551;A=5075;D=20120815
2 row has I=2166;A=4407.64;D=20120531|I=2500;A=90192.36;D=20120801
and this can be longer as per the allocations but each main part is separated by (|)
i want to extract parts namely I=character.
how do i do it since they are not uniform?
As you have a variable table, your problem statement is a bit complex for split string or any other SQL function. But you can search for Python data frame for this.
String spilt is not working in SQL2019
❤helped me
Glad it was helpful.