0

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Elina
  • 31
  • 1
  • 10

1 Answers1

1

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
Andrew
  • 7,602
  • 2
  • 34
  • 42
  • CharIndex are Null and number. – Elina Jun 23 '16 at 01:42
  • If it's NULL, it will return NULL, it won't produce an exception. Is it 0 for any row? For those rows it's not finding the comma. – Andrew Jun 23 '16 at 01:45
  • I change my script CHARINDEX(',', [CaseAttribute]) - 1) to CHARINDEX(',', [CaseAttribute]) - 0). And it works now! – Elina Jun 23 '16 at 01:46
  • but it removed my others (,) – Elina Jun 23 '16 at 01:48
  • But do you want to keep the comma or not? What's your desired output? – Andrew Jun 23 '16 at 01:49
  • I mean my string='Change shift,Change shift,DInex' and I want the result is: 1. Change shift 2. Change shift 3. DInex. And add them to other different columns. Is that clear? – Elina Jun 23 '16 at 01:52
  • But does your string always have 2 commas? Or it may have 1 or 3 or 4? If it's variable, check out this question: http://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns – Andrew Jun 23 '16 at 02:05
  • 1
    SELECT [CaseAttribute], PARSENAME(REPLACE([CaseAttribute],',','.'),3) 'Funtion' , PARSENAME(REPLACE([CaseAttribute],',','.'),2) 'Module' , PARSENAME(REPLACE([CaseAttribute],',','.'),1) 'Product' FROM view_CaseAttribute – Elina Jun 23 '16 at 02:17
  • Above sql script also support with NULL :) – Elina Jun 23 '16 at 02:18