0

I have a string like "How are you john?". I need to split the string based on user input index. The problem is words need not to split.

Example:

  • Input Query : How are you john?
  • Split Index : 5

I am getting output like this:

How are you john?

Expected output:

How
are 
you 
john?
Suraj Kumar
  • 5,547
  • 8
  • 20
  • 42
Gowdham
  • 153
  • 2
  • 3
  • 11
  • 1
    Please post your code. – Dale K Mar 07 '19 at 05:20
  • 1
    Ideally, do string processing in a *general purpose programming language* with good string manipulation facilities. Not T-SQL. As it is, I've got no idea *what* the relevance of "Split Index : 5" is - you don't seem to use it anywhere else in the question and the two answers posted so far seem to ignore it also. – Damien_The_Unbeliever Mar 07 '19 at 07:31
  • @Gowdham check my answer – Almazini Mar 08 '19 at 06:34

2 Answers2

0

You can try this.

Create Table tblData (inputString Varchar(50));    
Insert Into tblData Values('How are you john?')

SELECT   
     Split.a.value('.', 'VARCHAR(100)') AS String  
 FROM  (SELECT   
         CAST ('<M>' + REPLACE([inputString], ' ', '</M><M>') + '</M>' AS XML) AS String  
     FROM  tblData) AS A CROSS APPLY String.nodes ('/M') AS Split(a);
Suraj Kumar
  • 5,547
  • 8
  • 20
  • 42
  • This is dangerous, if your string could include forbidden characters. You might [read this](https://stackoverflow.com/a/38274439/5089204) to find an enhancement. As an alternative you can wrap the text in a `CDATA` section. – Shnugo Mar 07 '19 at 08:39
  • @Shnugo Thanks I will check it later. – Suraj Kumar Mar 07 '19 at 08:50
0

There is very good function STRING_SPLIT:

SELECT * 
FROM STRING_SPLIT ('How are you john?', ' ') AS cs;

This will give you desired output.

Second parameter can be anything you like e.g. ',' or '\'

SQL fiddle

Almazini
  • 1,825
  • 4
  • 25
  • 48
  • Worth to mention, that `STRING_SPLIT()` will need v2016+ and that the result is not guaranteed to be returned in the given order. Quite a draw back... There is a [workaround calling `OPENJSON`](https://stackoverflow.com/a/38275075/5089204) (needs v2016+ as well). And there are several approaches for versions below v2016 – Shnugo Mar 07 '19 at 08:37
  • @Shnugo yes you are right that is why I added link to documentation. Do you know when order will be different? I have never had a case. – Almazini Mar 07 '19 at 08:41
  • Well - uhm - no ;-) The point is: It is not guaranteed. The only fact guaranteed is: The **sorting of a resultset is random without an explicit `ORDER BY` on the outermost query**. In fact I'm pretty sure, that this will work in most cases. But (if you return a larger set), the engine might decide to work this down in parallel. Or if the egine decides to re-sort your set to perform a join operation, this might be changed... – Shnugo Mar 07 '19 at 10:03
  • @Shnugo well I think it is caused by fact that any result-set returned by SELECT (without ORDER BY clause) theoretically doesn't have guaranteed order and may be different in different conditions. Since here we also get result-set it behaves similarly ... in theory :) – Almazini Mar 07 '19 at 10:32
  • Yes, that's exactly the point: We know **for sure**, that the result set **can** be in any order. Very often we use an approach in various places. Just imagine, you find this reliably working in all tested cases and then create a VIEW or an iTVF using this and you call this in another query. Later steps migth mess it up... I'd just not rely on something potentially random... – Shnugo Mar 07 '19 at 10:45