April 1, 2013 at 9:39 pm
I am new to SQL SERVER 2008, trying to explore T-SQL
I am trying to split a sentence into words using some simple logic, but not able to verify blank spaces
Some thing wrong with the code not able to verify Blank spaces
==============================================
Alter proc Usp_split_words
@tbl_name varchar(100), @is varchar(100)
as
Begin
Declare @len int
Declare @sp-2 int -- Starting position
Declare @inc int --- increment
Declare @word varchar(100) -- repository to store gathered word
Declare @counter int -- counter till length of the string entered
Set @len = LEN(@is)
Set @sp-2 = 1
set @inc = 1
set @counter = 1
While @len + 1 > @counter
Begin
Print 'Substring gathered was ' + SUBSTRING(@is, @sp-2, @inc)
If Ltrim(RTrim(SUBSTRING(@is, @sp-2, @inc))) != char(32)
Begin
Set @word = Ltrim(@word) + Rtrim(SUBSTRING(@is, @sp-2, @inc))
Set @counter = @counter + 1
Set @inc= @inc + 1
end
else if Ltrim(RTrim(SUBSTRING(@is, @sp-2, @inc))) = ''
Begin
Print 'Word inserted will be ' + @word
Insert into Word_entry values (@word)
Set @word = ''
Set @counter = @counter +1
End
continue
Begin
Print ' sorry you entered blank spaces can not insert record'
end
End
End
Exec Usp_split_words 'Word_entry', 'The Quick Brown Fox Jumps over a lazy Dog'
April 2, 2013 at 1:24 am
If this is just a training exercise, fair enough. But this is not the best way to solve such a problem - in SQL Server, loops are used infrequently as there is a set-based solution to most problems (including this one).
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 2, 2013 at 8:06 am
I would agree with Phil that this is not the best way to split a string. I would go one step further and suggest that you have a function called Usp_split_words but it only sort of splits them. You added a bunch of other stuff to it to. If you want to learn how to split strings using t-sql follow the link in my signature about splitting strings.
From the code you posted it looks like all you are trying to do is allow the data to be inserted if there are no spaces. There is no need to split this and there is no need to loop. The following code will do that with little effort:
declare @word varchar(125) = 'The Quick Brown Fox Jumps over a lazy Dog'
if LEN(@word) = LEN(replace(@word, ' ', ''))
Print 'Word inserted will be ' + @word
--Insert into Word_entry values (@word)
else
Print 'sorry you entered blank spaces can not insert record'
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 2, 2013 at 8:20 am
Sorry Sir
I am trying to split sentence into words and insert into table as each word as a separate row
This is just practicing test
for some reason I am not able to figure out how to verify Blank spaces
obviously some thing wrong with blanks verification in my code
with your code I am able to remove spaces, which is good but will not solve my purpose
thanks for help
NK1526
April 2, 2013 at 8:28 am
nk1526 (4/2/2013)
Sorry SirI am trying to split sentence into words and insert into table as each word as a separate row
This is just practicing test
for some reason I am not able to figure out how to verify Blank spaces
obviously some thing wrong with blanks verification in my code
with your code I am able to remove spaces, which is good but will not solve my purpose
thanks for help
NK1526
You are learning a technique which you should rarely use & likely learning bad habits along the way.
Use the splitter mentioned by Sean for this task.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 4, 2013 at 10:03 am
May be this will help
________________________________________________________
declare @word varchar(125) = 'The Quick Brown Fox Jumps over a lazy Dog'
declare @swrd varchar(100)
declare @l int
while len(@word) > 0
Begin
set @l = CHARINDEX(' ',@word)
if @l = 0 -- No space found
set @swrd = @word
else
set @swrd = SUBSTRING( @word, 1, @l )
Print 'Word inserted will be ' + @swrd
set @word = SUBSTRING( @word, len(@swrd)+2, LEN(@word)-len(@swrd)+1 )
end
April 4, 2013 at 10:09 am
adesai 26520 (4/4/2013)
May be this will help________________________________________________________
declare @word varchar(125) = 'The Quick Brown Fox Jumps over a lazy Dog'
declare @swrd varchar(100)
declare @l int
while len(@word) > 0
Begin
set @l = CHARINDEX(' ',@word)
if @l = 0 -- No space found
set @swrd = @word
else
set @swrd = SUBSTRING( @word, 1, @l )
Print 'Word inserted will be ' + @swrd
set @word = SUBSTRING( @word, len(@swrd)+2, LEN(@word)-len(@swrd)+1 )
end
Why not use the far simpler approach I posted? It doesn't loop and it is super easy to understand.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 4, 2013 at 10:14 am
If I am not mistaken, the command just replaces/removes the spaces, and the user wants to separate the word from a sentence and add single word to the table.
April 4, 2013 at 10:28 am
adesai 26520 (4/4/2013)
If I am not mistaken, the command just replaces/removes the spaces, and the user wants to separate the word from a sentence and add single word to the table.
If you want to parse the values then please look at the splitter I mentioned previously. It will blow the doors off a while loop for performance when splitting stings.
declare @word varchar(125) = 'The Quick Brown Fox Jumps over a lazy Dog'
Insert YourTable
select Item from dbo.DelimitedSplit8K(@word, ' ')
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 4, 2013 at 11:01 am
Excellent!!!
Its time for new Doors!!!!
April 4, 2013 at 11:02 am
Excellent!!!
Its time for new Doors!!!!
April 4, 2013 at 11:52 am
Excellent!!!
Its time for new Doors!!!!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply