MSG 537 :Invalid length parameter passed to the LEFT or SUBSTRING function

Question

when i create a function in sql server 2012 , in this function

CREATE FUNCTION howords (@str nvarchar(50), @word nvarchar(50))
RETURNS int
AS
BEGIN
  DECLARE @tempstr nvarchar(max)
  DECLARE @space int
  DECLARE @count int
  DECLARE @size int
  SET @count = 0
  WHILE (LEN(@str) >= 0)
  BEGIN
    SET @space = CHARINDEX(' ', @str, 1)
    SET @tempstr = LEFT(@str, (@space - 1))
    IF (@tempstr = @word)
      SET @count = @count + 1
    SET @size = LEN(@tempstr)
    IF (((LEN(@str)) - @space) > 0)
      SET @str = SUBSTRING(@str, @space + 1, ((LEN(@str)) - @space))
    IF (((LEN(@str)) - @space) <= 0)
    BEGIN
      IF (@str = @word)
        SET @count = 0
      WHILE (LEN(@str) > 0)
      BEGIN
        SET @space = CHARINDEX(' ', @str + 1)
        SET @tempstr = LEFT(@str, (@space - 1))
        IF (@tempstr = @word)
          SET @count = @count + 1

        SET @size = LEN(@tempstr)
        IF (((LEN(@str)) - @space) > 0)
          SET @str = SUBSTRING(@str, @space + 1, ((LEN(@str)) - @space))
        IF (((LEN(@str)) - @space) <= 0)
        BEGIN
          IF (@str = @word)
            SET @count = @count + 1
          BREAK
        END
      END
    END
  END
  RETURN @count
END

when i exec this query

select dbo.howords ('hello my hello','hello' )

i want to give me count (2) but it give me an error

MSG 537 :Invalid length parameter passed to the LEFT or SUBSTRING function.

any help?

If you explain what the function is supposed to be doing then there might be a simpler way to express the logic. — Gordon Linoff, Apr 25 '18 at 10:50
I want to give me how many '#words in #str such as when i send to function "hello hello world","hello" the function must returns 2 — חסן סרחאן, Apr 25 '18 at 10:53
Does a word only get counted if it's between spaces (or at the start or end)? I.e. is `howords('rosetta','set')` 0? Also, any punctuation other than spaces to worry about? — Damien_The_Unbeliever, Apr 25 '18 at 10:55
Possible duplicate - https://stackoverflow.com/questions/9789225/number-of-times-a-particular-character-appears-in-a-string?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa — Abhishek, Apr 25 '18 at 10:56

score 2 · Accepted Answer · answered Apr 25 '18 at 10:57

2

Try this

DECLARE @tosearch VARCHAR(MAX)='Hello'  
DECLARE @string VARCHAR(MAX)='hello my hello'  

SELECT (DATALENGTH(@string)-DATALENGTH(REPLACE(@string,@tosearch,'')))/DATALENGTH(@tosearch)  
AS OccurrenceCount

answered Apr 25 '18 at 10:57

Sandeep

333
2
7

MSG 537 :Invalid length parameter passed to the LEFT or SUBSTRING function

1 Answers1