Here we show how you remove N characters from last of the column value help of SUBSTRING() and CHAR_LENGTH() methods.
Below is sample table with data
--
-- Table structure for table `user_info`
--
CREATE TABLE `user_info` (
`id` int(11) NOT NULL,
`name` varchar(20) DEFAULT NULL,
`email` varchar(255) NOT NULL,
`age` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `user_info` (`id`, `name`, `email`, `age`) VALUES
(1, 'Amit', 'amit@gmail.com', 20),
(2, 'Anil', 'anil@hotmail.com', 21),
(3, 'Mahesh', 'mahesh@gmail.com', 20),
(4, 'Arvind', 'arvind@outlook.com', 24),
(5, 'Amit', 'amit@gmail.com', 20),
(6, 'Vedant', 'vedant@yahoo.com', 21),
(7, 'Rajendra', 'rajendra@outlook.com', 23),
(8, 'Arvind', 'arvind@outlook.com', 24),
(9, 'Kuldeep', 'kuldeep@outlook.com', 19);
--
-- Indexes for table `user_info`
--
ALTER TABLE `user_info`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for table `user_info`
--
ALTER TABLE `user_info`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;
COMMIT;
Here we strip last 4 characters from email column values.
Syntax:
SELECT SUBSTRING(Column_Name,1,CHAR_LENGTH(Column_Name) - n) AS Variable_Name FROM Table_Name;
Query
SELECT SUBSTRING(`email`,1,CHAR_LENGTH(`email`) - 4) AS strip_email FROM `user_info`;
OUTPUT
I hope this article helps you.
Thanks for visiting Inflay.com .