CODEWITHSUNDEEP
mysqlsql
mohan111
mohan111

Reputation: 8865

How to remove the first special character in MYSQL

I have sample Data

+----+-----------+
| Id | Name      |
+----+-----------+
|  1 | $John     |
|  2 | $Carol    |
|  3 | $Mike     | 
|  4 | $Sam      |
|  5 | $David$Mohan$ |
|  6 | Robert$Ram$   |  
|  8 | Maxwell$ |
+----+-----------+

I need to remove the only $ first character

Need output : 

+----+-----------+
| Id | Name      |
+----+-----------+
|  1 | John     |
|  2 | Carol    |
|  3 | Mike     | 
|  4 | Sam      |
|  5 | David$Mohan$ |
|  6 | Robert$Ram$   |  
|  8 | Maxwell$ |
+----+-----------+

Select REPLACE(col,'$','') from Tbl

I have tried with Replace and Substring but still missing the point . Can anyone suggest me .

Upvotes: 0

Views: 176

Answers (3)

GMB
GMB

Reputation: 222482

If you are running MySQL 8.0, you can do this with just on invokation of regexp_replace():

select regexp_replace(name, '^$', '') name from mytable

Regexp '^$' means: a dollar sign at the beginning of the string. If the string does not match the regexp, regexp_replace() returns it untouched.

Upvotes: 1

apomene
apomene

Reputation: 14389

SELECT 
CASE
    WHEN SUBSTRING(Name, 1, 1) = '$' THEN SUBSTRING(Name, 2, LENGTH(Name)-1)   
    ELSE Name
END
FROM myTable;

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can use a case expression and string operations:

(case when col like '$%' then substr(col, 2) else col end)

Upvotes: 2

Related Questions