I have table with values. It is ntext because of ; delimited. Values can be empty, 1 number and numbers delimited by semicolon (as shown)
+-----------+
| room |
+-----------+
| 64 |
+-----------+
| 60008 |
+-----------+
| |
+-----------+
| 127;50047 |
+-----------+
64,60008, ,127
SELECT
T0.U_Scid as 'id',
T3.U_Boarding as 'start',
T3.U_Boarding as 'end',
SUBSTRING(T5.U_Partner, 0, CHARINDEX(';', T5.U_Partner)) AS 'room_id',
CASE WHEN datalength(T5.U_Partner)=0 THEN '9999' ELSE T5.U_Partner END AS 'room_id' ,
CASE WHEN datalength(T5.U_Partner) > 4 THEN T5.U_Partner ELSE '9999' END AS 'partners_id' ,
Here is a simple method:
SUBSTRING(T5.U_Partner, 1, CHARINDEX(';', T5.U_Partner + ';')) AS room_id,
That is, concatenate the semicolon to the argument for CHARINDEX()
. That will prevent any error occurring.
In addition, indexing for SUBSTRING()
starts at 1, not 0.
And, don't use single quotes for column names. Only use them for string and date literals.
EDIT:
You can always use the verbose form:
(CASE WHEN T5.U_Partner LIKE '%;%'
THEN SUBSTRING(T5.U_Partner, 1, CHARINDEX(';', T5.U_Partner + ';'))
ELSE T5.U_Partner
END) AS room_id