How did the cross-apply work here as we have not provided any where condition. Also, I believe if we don’t apply the where condition then it connect every row of first table with another, something like cross join. Can you pls help me to understand? Thanks
Hi Vivek. Cross Apply is not the same as Cross Join. Cross Apply looks at each row, and applies the function (in this case, STRING_SPLIT) for each row. If the function gives a NULL, then the row is withheld (an OUTER APPLY would retain that row). Phillip
Dear Nikhil, Thank you for your question. Very tricky. Instead of using the delimiter '.' (no space), you should use the delimiter '. ' (with a space). This means that 'B.B.C.' would remain as is. However, you can't use STRING_SPLIT with a separator of more than 1 character. This means you should use REPLACE to replace '. ' (with a space) by a character which is cannot possibly be in the string. I would use `, but for readability, in the next few examples, I will use +. You could use: DECLARE @var varchar(80) = 'For true trails addicts. An extremely durable B.B.C. bike.' SELECT * FROM string_split(replace(@var,'. ','+'),'+') This results in: For true trails addicts An extremely durable B.B.C bike. Alternately, you should change B.B.C. to the +, and then change it back - for example: DECLARE @var varchar(80) = 'For true trails addicts. An extremely durable B.B.C. bike.' SELECT replace([value],'+','B.B.C') as [value] FROM string_split(replace(@var,'B.B.C.','+'),'.') This results in: For true trails addicts An extremely durable B.B.C bike I hope this helps. Regards, Phillip
Hi Suppose i am having a word like "Server" As input. My output should be in differrnr lines. Output : S E R V E R This is simple in python. But not sure how to do it in sql. Repeated sql question in interview Thanks in advance
Hi Dinesh. Please have a look at stackoverflow.com/questions/8517816/t-sql-split-word-into-characters . It's a very good question - I'll add it to my list of videos to create. Phillip
@@SQLServer101 Thanks for reply dude, I am looking for solution/query on this. On how to query the space between the capital letters, thats why I saw your tutorials dudes and asking it possible you know the query, thanks for answer
love this video - easy & straight forward
clear english is an asset here :D
Thank you very much! Very informative and saved my time.
How did the cross-apply work here as we have not provided any where condition. Also, I believe if we don’t apply the where condition then it connect every row of first table with another, something like cross join. Can you pls help me to understand? Thanks
Hi Vivek. Cross Apply is not the same as Cross Join. Cross Apply looks at each row, and applies the function (in this case, STRING_SPLIT) for each row. If the function gives a NULL, then the row is withheld (an OUTER APPLY would retain that row). Phillip
Can we have a video on how to do row versioning?
I don't have the split function, I am using the 2012 version, any suggestions on how to split 4 strings in one column into 4.
Hi Solomong. Please have a look at stackoverflow.com/questions/46902892/string-split-in-sql-server-2012 . Phillip
Help but if we want to split based on length,60 characters in each row
Hi Prith. Thank you for your suggestion. That's a good idea - I'll add it to my "to do" list. Phillip
I am using 2016 version, SPLIT_STRING is not available. Can we do without SPLIT_STRING?
Hi Uday. You can - but it's not easy. Have a look at this webpage for details: sqlperformance.com/2021/09/t-sql-queries/split-strings . Phillip
What if B.C.C. is present ? And we don't want to split B.C.C.
Please Provide solution
Dear Nikhil,
Thank you for your question.
Very tricky. Instead of using the delimiter '.' (no space), you should use the delimiter '. ' (with a space). This means that 'B.B.C.' would remain as is. However, you can't use STRING_SPLIT with a separator of more than 1 character.
This means you should use REPLACE to replace '. ' (with a space) by a character which is cannot possibly be in the string. I would use `, but for readability, in the next few examples, I will use +. You could use:
DECLARE @var varchar(80) = 'For true trails addicts. An extremely durable B.B.C. bike.'
SELECT *
FROM string_split(replace(@var,'. ','+'),'+')
This results in:
For true trails addicts
An extremely durable B.B.C
bike.
Alternately, you should change B.B.C. to the +, and then change it back - for example:
DECLARE @var varchar(80) = 'For true trails addicts. An extremely durable B.B.C. bike.'
SELECT replace([value],'+','B.B.C') as [value]
FROM string_split(replace(@var,'B.B.C.','+'),'.')
This results in:
For true trails addicts
An extremely durable B.B.C bike
I hope this helps.
Regards,
Phillip
Hi Suppose i am having a word like "Server" As input.
My output should be in differrnr lines.
Output :
S
E
R
V
E
R
This is simple in python. But not sure how to do it in sql. Repeated sql question in interview
Thanks in advance
Hi Dinesh. Please have a look at stackoverflow.com/questions/8517816/t-sql-split-word-into-characters . It's a very good question - I'll add it to my list of videos to create. Phillip
what about in Postgresql?
Hi Sushugowda. This channel is about Microsoft SQL Server, not PostgreSQL. Phillip
I have a question: How to Separate a word like this "Howtoseparatethiswordinonecolum" and the result is "this How to separate this word in one colum"
I don't know that you can. You would need the computer to know where each word starts without any clues. Phillip
@@SQLServer101 Thanks for reply dude, I am looking for solution/query on this. On how to query the space between the capital letters, thats why I saw your tutorials dudes and asking it possible you know the query, thanks for answer
Thank you very much
nice!