show value from text..if delimited by ; show only first value

Question

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, ,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' ,

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?

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

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

show value from text..if delimited by ; show only first value

Sign up