I have one table. In that I am storing user details there like firstname, lastname, phonenumber. while storing phone number it stores data without extension like +1(013)691-1535x___ . Sometime user might enter extension also that it will store like +1(013)691-1535x12345. Here my doubt is how to retrieve only phone number if extension is in empty. If extension is avaiable I need to view phone number with extension.
4
-
did you store extension in separated columncodercat– codercat2013-12-30 10:18:22 +00:00Commented Dec 30, 2013 at 10:18
-
No I am storing in same column.Sathish Babu– Sathish Babu2013-12-30 10:26:43 +00:00Commented Dec 30, 2013 at 10:26
-
followed by X you should be stored the value of extension ahcodercat– codercat2013-12-30 10:29:20 +00:00Commented Dec 30, 2013 at 10:29
-
Yes. I am storing extension followed by XSathish Babu– Sathish Babu2013-12-30 10:33:31 +00:00Commented Dec 30, 2013 at 10:33
Add a comment
|
2 Answers
You can split the phone number using SUBSTRING_INDEX :
SELECT phone_number,SUBSTRING_INDEX(phone_number, 'x', 1) as phone_num, SUBSTRING_INDEX(phone_number, 'x', -1) as phone_ext FROM tablename;
Query result
+-----------------------+-----------------+-----------+
| phone_number | phone_num | phone_ext |
+-----------------------+-----------------+-----------+
| +1(013)691-1535x12345 | +1(013)691-1535 | 12345 |
+-----------------------+-----------------+-----------+
Now, you have entire phone number with extension, phone number without extension and only extension. Depending on the query result, you can have a 'check' in your script and use the field value.
1 Comment
Sathish Babu
I have to see both in the same result. if extension is available it should show, otherwise no need . I donot want results in splited columns. thanks
try like or Well if you know the order of your words.. you can use REGEXP
SELECT `phone_number` FROM `table` WHERE `phone_number` LIKE '+1(013)691-1535x%'
