CODEWITHSUNDEEP
mysql
user10828115
user10828115

Reputation:

Removing part of a string in column

I am trying to remove part of a string. But can't do it properly. String is like this: 4,290TL〜9,490TL So trying to remove after this 〜　

I tried

UPDATE SET price = SUBSTRING_INDEX(price, '〜')

But not worked.

Upvotes: 0

Views: 51

Answers (3)

Paul Maxwell
Paul Maxwell

Reputation: 35563

Please note the the strings you have shared with this question or comments uses DIFFERENT wavy line ("tilde") characters 

# the tilde character used here is DIFFERENT to the next example
select substring_index('4,290TL〜9,490TL','〜',1) 
;
+----+------------------------------------------------------+
|    |      substring_index('4,290TL〜9,490TL','〜',1)      |
+----+------------------------------------------------------+
|  1 | 4,290TL                                              |

# the tilde character is different to the one above
select substring_index('18,990万円(1戸)～28,790万円(1戸)','～',1) 
;
+----+-----------------------------------------------------------+
|    | substring_index('18,990万円(1戸)～28,790万円(1戸)','～',1) |
+----+-----------------------------------------------------------+
|  1 | 18,990万円(1戸)                                           |

You will need to be CERTAIN the the tilde you use as delimiter is the one you use in substring_index() otherwise that function will just return the whole string.

Upvotes: 0

Alvin Nguyen
Alvin Nguyen

Reputation: 248

UPDATE SET price = SUBSTRING_INDEX(price, '〜', 1)

Upvotes: 0

Nick
Nick

Reputation: 147146

SUBSTRiNG_INDEX requires 3 parameters, the last one being the delimiter count. In this case you need to supply a count of 1, indicating that you want everything to the left of the first occurrence of the delimiter 〜. Additionally, you need to specify your table name in the query. Try this:

UPDATE yourtable SET price = SUBSTRING_INDEX(price, '〜', 1)

Upvotes: 1

Related Questions