SQL Question
show value from text..if delimited by ; show only first value
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 |
+-----------+
I have this code. Substring is looking for ; and show first value. It is working only where the values are delimited. So how can I change it, that it will show first value when ; and single value also. So from table bellow I will get
64,60008, ,127SELECT
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' ,
This is just bonus question. CASE are looking for length of value, if the value is longer than 4 ( 600008 ) write to room_id 9999 and save 600008 to partners_id. If it is empty write 9999 to room_id.
How to make it works together?.so getting value from T_Partner..save it into temporary table T1.TempRoom ( I suppose ).. so T1.TempRoom (is filled with numbers like 64, ,60008,127) then CASE is checking T1.TempRoom for values and save it into room_id and partners_id.
Am I right?
Answer
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
Source (Stackoverflow)