Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

how to verify blank spaces using substr function in SQLSERVER 2008 Expand / Collapse
Author
Message
Posted Monday, April 1, 2013 9:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 5, 2013 9:34 AM
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'
Post #1437681
Posted Monday, April 1, 2013 10:06 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 21, 2014 6:46 AM
Points: 1,059, Visits: 697
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...
Post #1437682
Posted Tuesday, April 2, 2013 1:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 5,045, Visits: 11,794
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1437727
Posted Tuesday, April 2, 2013 8:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:04 PM
Points: 13,296, Visits: 12,147
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)
Post #1437908
Posted Tuesday, April 2, 2013 8:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 5, 2013 9:34 AM
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
Post #1437923
Posted Tuesday, April 2, 2013 8:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 5,045, Visits: 11,794
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1437928
Posted Thursday, April 4, 2013 10:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 11, 2013 11:26 AM
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
Post #1438886
Posted Thursday, April 4, 2013 10:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:04 PM
Points: 13,296, Visits: 12,147
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)
Post #1438890
Posted Thursday, April 4, 2013 10:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 11, 2013 11:26 AM
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.
Post #1438893
Posted Thursday, April 4, 2013 10:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:04 PM
Points: 13,296, Visits: 12,147
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)
Post #1438901
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse