Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


how to verify blank spaces using substr function in SQLSERVER 2008


how to verify blank spaces using substr function in SQLSERVER 2008

Author
Message
nk1526
nk1526
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 11
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 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 = 1
set @inc = 1
set @counter = 1

While @len + 1 > @counter
Begin

Print 'Substring gathered was ' + SUBSTRING(@is, @sp, @inc)

If Ltrim(RTrim(SUBSTRING(@is, @sp, @inc))) != char(32)
Begin
Set @word = Ltrim(@word) + Rtrim(SUBSTRING(@is, @sp, @inc))
Set @counter = @counter + 1
Set @inc= @inc + 1
end
else if Ltrim(RTrim(SUBSTRING(@is, @sp, @inc))) = ''
Begin
Print 'Word inserted will be ' + @word
Insert into Word_entry values (@word)
Set @word = ''
set @sp = @sp + 1
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'
Pulivarthi Sasidhar
Pulivarthi Sasidhar
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1448 Visits: 992
Use Charindex function...

the following example will bring string from first letter to First Space

SUBSTRING( @str, 1, CHARINDEX(' ',@str) )

U can loop the string until u reach End of string...
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8411 Visits: 19512
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).


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16671 Visits: 17030
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
nk1526
nk1526
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 11
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
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8411 Visits: 19512
nk1526 (4/2/2013)
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


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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
adesai 26520
adesai 26520
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 46
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
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16671 Visits: 17030
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
adesai 26520
adesai 26520
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 46
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.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16671 Visits: 17030
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search