question

shriram avatar image
shriram asked

How to remove first letter if it is number in sql?

**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 those was occuring only in the first position of the data. 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
sqlmysql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
Use LIKE to look for a pattern that starts with any number `[0-9]%` and take a substring of the original value, otherwise leave as is declare @YourTable table (DataValue varchar(100)) insert into @YourTable (DataValue) select 'Psalm. 121:7' insert into @YourTable (DataValue) select '1Peter 3:4' insert into @YourTable (DataValue) select 'James 1:22' insert into @YourTable (DataValue) select '2Timothy 4:2' select case when DataValue like '[0-9]%' then substring(DataValue,2,len(DataValue)) else DataValue end from @YourTable -------------------------- Psalm. 121:7 Peter 3:4 James 1:22 Timothy 4:2 (4 rows affected)
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

shriram avatar image shriram commented ·
Thanks For Your Reply.. But in example only i mentioned 2nd and 4th value actually i will be random so can i change this? "when DataValue like '[0-9]%' then substring(DataValue,2,len(DataValue))"
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
Sorry, I don't understand. What will be random?
0 Likes 0 ·
shriram avatar image shriram commented ·
Thanks for your reply. I am getting solution Update my_table set my_column = right(my_column, LENGTH(my_column)-1) WHERE my_column REGEXP '^[0-9]' anyway thanks.....
0 Likes 0 ·
Jon Crawford avatar image
Jon Crawford answered
Shriram, for the data given, your result becomes meaningless, as there is no book named "Peter" or "Timothy" in the Christian Bible. If your data reflects what you are trying to do, you may want to reconsider your intent. The only think I can think is that you are trying to use this for sort order alphabetically or something? But in that case you might want to consider the order of the books in the content anyway, and not alphabetically. You would also want to rank anything from 2Peter after 1Peter, so you'd have to reconsider that first character which you removed in order to do so.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

shriram avatar image shriram commented ·
Thanks for your reply. I am getting solution Update my_table set my_column = right(my_column, LENGTH(my_column)-1) WHERE my_column REGEXP '^[0-9]' anyway thanks.....
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.