Ggtcf

Getting the || sign while using Kurier

How to resolve: Reviewer #1 says remove section X vs. Reviewer #2 says expand section X

From an axiomatic set theoric approach why can we take uncountable unions?

Expressing logarithmic equations without logs

Why do we say ‘pairwise disjoint’, rather than ‘disjoint’?

Specifying a starting column with colortbl package and xcolor

Why does cron require MTA for logging?

Why aren't there more Gauls like Obelix?

Haman going to the second feast dirty

What is the generally accepted pronunciation of “topoi”?

Outlet with 3 sets of wires

PTIJ: Why does only a Shor Tam ask at the Seder, and not a Shor Mu'ad?

Is it safe to abruptly remove Arduino power?

Shifting between bemols (flats) and diesis (sharps)in the key signature

Can we track matter through time by looking at different depths in space?

Does a difference of tense count as a difference of meaning in a minimal pair?

School performs periodic password audits. Is my password compromised?

What is Tony Stark injecting into himself in Iron Man 3?

What is this diamond of every day?

Is it a Cyclops number? "Nobody" knows!

When a wind turbine does not produce enough electricity how does the power company compensate for the loss?

Plausibility of Mushroom Buildings

What is better: yes / no radio, or simple checkbox?

What can I do if someone tampers with my SSH public key?

How to split string in SQL Server? [duplicate]

0

This question already has an answer here:

• 
  Turning a Comma Separated string into individual rows
  
  14 answers
  
  

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?

sql-server

share|improve this question

edited Mar 7 at 5:20

Suraj Kumar

2,6943825

asked Mar 7 at 5:07

GowdhamGowdham

3516

marked as duplicate by Gábor Bakos, Larnu sql-server
Users with the  sql-server badge can single-handedly close sql-server questions as duplicates and reopen them as needed.

StackExchange.ready(function()
if (StackExchange.options.isMobile) return;

$('.dupe-hammer-message-hover:not(.hover-bound)').each(function()
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');

$hover.hover(
function()
$hover.showInfoMessage('',
messageElement: $msg.clone().show(),
transient: false,
position: my: 'bottom left', at: 'top center', offsetTop: -7 ,
dismissable: false,
relativeToBody: true
);
,
function()
StackExchange.helpers.removeMessages();

);
);
);
Mar 7 at 10:59

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Please post your code.
  
  – Dale Burrell
  Mar 7 at 5: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 7 at 7:31
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Gowdham check my answer
  
  – Almazini
  2 days ago
  

  
  
  
  

add a comment | 

0

This question already has an answer here:

• 
  Turning a Comma Separated string into individual rows
  
  14 answers
  
  

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?

sql-server

share|improve this question

edited Mar 7 at 5:20

Suraj Kumar

2,6943825

asked Mar 7 at 5:07

GowdhamGowdham

3516

marked as duplicate by Gábor Bakos, Larnu sql-server
Users with the  sql-server badge can single-handedly close sql-server questions as duplicates and reopen them as needed.

StackExchange.ready(function()
if (StackExchange.options.isMobile) return;

$('.dupe-hammer-message-hover:not(.hover-bound)').each(function()
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');

$hover.hover(
function()
$hover.showInfoMessage('',
messageElement: $msg.clone().show(),
transient: false,
position: my: 'bottom left', at: 'top center', offsetTop: -7 ,
dismissable: false,
relativeToBody: true
);
,
function()
StackExchange.helpers.removeMessages();

);
);
);
Mar 7 at 10:59

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Please post your code.
  
  – Dale Burrell
  Mar 7 at 5: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 7 at 7:31
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Gowdham check my answer
  
  – Almazini
  2 days ago
  

  
  
  
  

add a comment | 

This question already has an answer here:

• 
  Turning a Comma Separated string into individual rows
  
  14 answers
  
  

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?

sql-server

share|improve this question

edited Mar 7 at 5:20

Suraj Kumar

2,6943825

asked Mar 7 at 5:07

GowdhamGowdham

3516

How are you john?

How
are 
you 
john?

sql-server

sql-server

share|improve this question

edited Mar 7 at 5:20

Suraj Kumar

2,6943825

asked Mar 7 at 5:07

GowdhamGowdham

3516

share|improve this question

edited Mar 7 at 5:20

Suraj Kumar

2,6943825

asked Mar 7 at 5:07

GowdhamGowdham

3516

edited Mar 7 at 5:20

Suraj Kumar

2,6943825

edited Mar 7 at 5:20

Suraj Kumar

2,6943825

asked Mar 7 at 5:07

GowdhamGowdham

3516

asked Mar 7 at 5:07

GowdhamGowdham

3516

2 Answers
2

active

oldest

votes

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);

share|improve this answer

answered Mar 7 at 5:36

Suraj KumarSuraj Kumar

2,6943825

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This is dangerous, if your string could include forbidden characters. You might read this to find an enhancement. As an alternative you can wrap the text in a CDATA section.
  
  – Shnugo
  Mar 7 at 8:39
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Shnugo Thanks I will check it later.
  
  – Suraj Kumar
  Mar 7 at 8:50
  

  
  
  
  

add a comment | 

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

share|improve this answer

answered Mar 7 at 7:17

AlmaziniAlmazini

95921739

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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 (needs v2016+ as well). And there are several approaches for versions below v2016
  
  – Shnugo
  Mar 7 at 8: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 7 at 8: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 7 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 7 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 7 at 10:45
  

  
  
  
  

add a comment | 

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);

share|improve this answer

answered Mar 7 at 5:36

Suraj KumarSuraj Kumar

2,6943825

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This is dangerous, if your string could include forbidden characters. You might read this to find an enhancement. As an alternative you can wrap the text in a CDATA section.
  
  – Shnugo
  Mar 7 at 8:39
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Shnugo Thanks I will check it later.
  
  – Suraj Kumar
  Mar 7 at 8:50
  

  
  
  
  

add a comment | 

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);

share|improve this answer

answered Mar 7 at 5:36

Suraj KumarSuraj Kumar

2,6943825

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This is dangerous, if your string could include forbidden characters. You might read this to find an enhancement. As an alternative you can wrap the text in a CDATA section.
  
  – Shnugo
  Mar 7 at 8:39
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Shnugo Thanks I will check it later.
  
  – Suraj Kumar
  Mar 7 at 8:50
  

  
  
  
  

add a comment | 

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);

share|improve this answer

answered Mar 7 at 5:36

Suraj KumarSuraj Kumar

2,6943825

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);

share|improve this answer

answered Mar 7 at 5:36

Suraj KumarSuraj Kumar

2,6943825

answered Mar 7 at 5:36

Suraj KumarSuraj Kumar

2,6943825

answered Mar 7 at 5:36

Suraj KumarSuraj Kumar

2,6943825

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

share|improve this answer

answered Mar 7 at 7:17

AlmaziniAlmazini

95921739

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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 (needs v2016+ as well). And there are several approaches for versions below v2016
  
  – Shnugo
  Mar 7 at 8: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 7 at 8: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 7 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 7 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 7 at 10:45
  

  
  
  
  

add a comment | 

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

share|improve this answer

answered Mar 7 at 7:17

AlmaziniAlmazini

95921739

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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 (needs v2016+ as well). And there are several approaches for versions below v2016
  
  – Shnugo
  Mar 7 at 8: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 7 at 8: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 7 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 7 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 7 at 10:45
  

  
  
  
  

add a comment | 

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

share|improve this answer

answered Mar 7 at 7:17

AlmaziniAlmazini

95921739

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

share|improve this answer

answered Mar 7 at 7:17

AlmaziniAlmazini

95921739

answered Mar 7 at 7:17

AlmaziniAlmazini

95921739

answered Mar 7 at 7:17

AlmaziniAlmazini

95921739