CODEWITHSUNDEEP
sql-server
Elina
Elina

Reputation: 31

Message error "Invalid length parameter passed to the LEFT or SUBSTRING function."

That is my SQL Server script:

SELECT
    [CaseAttribute],
    LEFT([CaseAttribute], CHARINDEX(',', [CaseAttribute]) - 1) AS [Function],
    REPLACE(SUBSTRING([CaseAttribute], CHARINDEX(',', [CaseAttribute]), LEN([CaseAttribute])), ',', '') AS [Module]
FROM 
    view_CaseAttribute

and my string = 'Change shift,Change shift,DInex'. When I run the script I get an error:

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

Upvotes: 0

Views: 7992

Answers (1)

Andrew
Andrew

Reputation: 7880

Well, obviously it's not finding any comma, so the result from CHARINDEX is 0 and LEFT is receiving a -1. Didn't you try modifying your query to find out what's happening? Something like this, for example:

SELECT
    [CaseAttribute],
    CHARINDEX(',', [CaseAttribute]) AS CommaIndex
FROM view_CaseAttribute
ORDER BY CHARINDEX(',', [CaseAttribute]) ASC

Another option, to avoid the error:

CASE WHEN CHARINDEX(',', [CaseAttribute]) > 0 THEN LEFT([CaseAttribute], CHARINDEX(',', [CaseAttribute]) - 1) END AS [Function],

In the end, this was the solution Elina adopted:

SELECT [CaseAttribute],
    PARSENAME(REPLACE([CaseAttribute], ',', '.'), 3) 'Function',
    PARSENAME(REPLACE([CaseAttribute], ',', '.'), 2) 'Module',
    PARSENAME(REPLACE([CaseAttribute], ',', '.'), 1) 'Product'
FROM view_CaseAttribute

Upvotes: 1

Related Questions