Menu
I have a column with few different ID's
abc_1234
abc_2345
bcd_3456/
cde_4567/
And I want a new column that takes off the / if it exists
abc_1234
abc_2345
bcd_3456
cde_4567
I know I'll be using a combination of IF/THEN, LEFT, and LEN, but I don't know the syntax. Help is appreciated! Thanks!
544
To get the first n characters of string with MySQL, use LEFT(). To get the last n char of string, the RIGHT() method is used in MySQL.
The TRIM() function removes the space character OR other specified characters from the start or end of a string. By default, the TRIM() function removes leading and trailing spaces from a string. Note: Also look at the LTRIM() and RTRIM() functions.
Below is the syntax for the SUBSTRING() function to delete the last N characters from the field. Syntax: SELECT SUBSTRING(column_name,1,length(column_name)-N) FROM table_name; Example: Delete the last 2 characters from the FIRSTNAME column from the geeksforgeeks table.
To delete the first characters from the field we will use the following query: Syntax: SELECT SUBSTRING(string, 2, length(string));
(In case your are using SQL Server RDBMS)
You can try the following combination of right and left:
case when right(col, 1) = '/' then left(col, len(col)-1) else col end
SQLFiddle
(In case your are using MySQL RDBMS)
trim(trailing '/' from col);
SQLFiddle
103
If your using SQL Server try this
SELECT REPLACE(col,'/','')
Replace (Transact-SQL)
30
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
