Re: How to Split value in Varcahar field

[John H. Embretsen]

Praveen,

Using the TRIM function should do the trick.

For example, consider the following table:

mysql> SELECT * FROM t1;
+------------------+
| SenderCompID     |
+------------------+
| SenderCompID(49) |
| AnotherID(47)    |
+------------------+
2 rows in set (0.00 sec)

Then this query should give you the separated values:

mysql> SELECT SUBSTRING_INDEX(SenderCompID, '(', 1) AS value1, TRIM(TRAILING ')' FROM SUBSTRING_INDEX(SenderCompID, '(', -1)) AS value2 FROM t1;
+--------------+--------+
| value1       | value2 |
+--------------+--------+
| SenderCompID | 49     |
| AnotherID    | 47     |
+--------------+--------+
2 rows in set (0.00 sec)

--
John