how to verify blank spaces using substr function in SQLSERVER 2008

  • 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 @sp-2 = @sp-2 + 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'

  • 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...

  • 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.

  • 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/

  • 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

  • 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.

    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.

  • 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

  • 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/

  • 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.

  • 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/

  • Excellent!!!

    Its time for new Doors!!!!

  • Excellent!!!

    Its time for new Doors!!!!

  • 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