-2

I have the following code but I am getting the error,

Msg 537, Level 16, State 3, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.

Code:

SELECT        
    Main.HostName, LEFT(Main.Users, Len(Main.Users) - 1) AS [Users]
FROM            
    (SELECT DISTINCT 
         ST2.HostName,
         (SELECT ST1.UserName + ', ' AS [text()]
          FROM dbo.USERS ST1
          WHERE ST1.HostName = ST2.HostName
          ORDER BY ST1.HostName FOR XML PATH('')) [Users]
     FROM           
         dbo.USERS ST2) [Main]

I am trying to left most user in each row.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dean Flaherty
  • 351
  • 1
  • 5
  • 15
  • Any chance you have a Users with 0 character? – DVT Dec 06 '16 at 18:59
  • Check Main.Users column, it could be null or ''. – Serg Dec 06 '16 at 19:00
  • This is easily found by using google. – dfundako Dec 06 '16 at 19:23
  • Possible duplicate of [SQL charindex throwing Invalid length parameter passed to the LEFT or SUBSTRING function because of period?](http://stackoverflow.com/questions/9946833/sql-charindex-throwing-invalid-length-parameter-passed-to-the-left-or-substring) – AHiggins Dec 06 '16 at 19:31
  • Yes I added + ', ' after the Len(Main.Users) and that ran my query but each cell now ends in ','. Do you know know to resolve this? – Dean Flaherty Dec 07 '16 at 07:28

1 Answers1

1

You have Main.Users with an empty string (len = 0)

David דודו Markovitz
  • 42,900
  • 6
  • 64
  • 88