-5


How to remove first letter if it is number in sql?
I have some values in my sql
(i.e)

Psalm. 121:7
1Peter 3:4
James 1:22
2Timothy 4:2

Here 1st and 3rd values are correct but 2nd and 4th value i need to remove numbers. How to do this via sql query?

I want the result

Psalm. 121:7 = Psalm. 121:7
1Peter 3:4 = Peter 3:4
James 1:22 = James 1:22
2Timothy 4:2 = Timothy 4:2

shri ram
  • 15
  • 7
  • I think you need a `regex replace`. 1. Wrote your user defined function (UDF) to implement regex to replace 2. Switch to MariaDB which has a build in function. – Jerry Chen Jan 22 '18 at 06:29
  • you can check the answer [here](https://stackoverflow.com/questions/13855053/removing-numbers-from-string-in-mysql). – Sibaprasad Maiti Jan 22 '18 at 06:39
  • but it remove all numbers in my data. i dont want this. i need remove 1st character only,if its number @SibaprasadMaiti – shri ram Jan 22 '18 at 06:44

2 Answers2

0

You could use the RIGHT() part of the string filtering wher the firts is a number eg: 

SELECT  right(my_column, LENGTH(my_column)-1)
FROM    my_table
WHERE   my_column REGEXP '^[0-9]'

for update (remove the number ) you could use

Update my_table
set my_column=  right(my_column, LENGTH(my_column)-1)

WHERE   my_column REGEXP '^[0-9]'
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
0

You can use a function like below 

CREATE DEFINER=`root`@`localhost` FUNCTION `stripFirstNumber`(in_str varchar(100)) RETURNS varchar(100) CHARSET latin1
BEGIN

declare first_char varchar(1);
declare len integer;
declare ret_val varchar(100);

set len = length(in_str);
if len > 0 then
    set first_char = substring(in_str, 1, 1);
    if find_in_set(first_char, '0,1,2,3,4,5,6,7,8,9') then
        return substring(in_str, 2, len-1);
    else
        return in_str;
    end if;
end if;

RETURN ret_val;
END
Sibaprasad Maiti
  • 507
  • 8
  • 19