CODEWITHSUNDEEP
mysql
mattew
mattew

Reputation: 203

Reading a number in a specific position

I have a table having a field of telephone numbers. I need to update the "districtname" field in function of the 3rd number from left.

 UPDATE mytable
 SET districtname =
 CASE
    WHEN the3rdnumberfromleft = '8' THEN 'Mycountryname'
    ELSE ''
 END

How can I set the condition to read the number in that specific position?

Upvotes: 0

Views: 49

Answers (2)

Abubakkar
Abubakkar

Reputation: 15644

You can use LIKE with wildcard character in MySql

UPDATE mytable 
   SET districtname = 'yourDesiredName'
WHERE telephone LIKE '__8%'

Explanation:

__8% will match any telephone number that has number 8 at third position from left.

Note there are two _ (underscore) characters before 8 meaning match first two (one for each) to any character

And then comes 8 so it will only match for 8

And then comes % meaning match any characters afterwards.

For more on this, refer this link

Upvotes: 1

O. Jones
O. Jones

Reputation: 108651

If I read your question correctly you're looking to update only some of the rows in your table. If that's true, try this.

 UPDATE mytable 
    SET districtname = 'Mycountryname'
  WHERE SUBSTRING(LTRIM(telephone),3,1) = '8'

This will choose only the rows with the 8 as you mentioned. I threw in LTRIM to cope with the situation where your telephone number might have spaces before it. You don't need that if you're sure all your telephone numbers are left-justified.

If the telephone number is stored in an integer field, this will still work because MySQL implicitly converts integers you use in as if they were strings.

Upvotes: 0

Related Questions