Removing part of string after specific character using mysql

Question

I want to remove the all part of characters after the specific character in mysql column. But i need that mentioned removable character. I have searched but everything the mentioned characters also removed.

For Example:

Tracding Company Pvt Ltd. - Welecome Home

Need output:

Tracding Company Pvt Ltd.

If i use Ltd. with substring_index then the ltd. also removed.

Please someone help with this.

Possible duplicate of SQL Server replace, remove all after certain character — Muhammad Vakili
– Muhammad Vakili, Commented Jul 3, 2019 at 10:56
@Vakili, But if i use CHARINDEX functioon, its shows the following error, " FUNCTION CHARINDEX does not exist". Also, If use the certain character that character no need o remove. — Vinod
– Vinod, Commented Jul 3, 2019 at 11:08
Sorry, CHARINDEX function is used in MSSQL, you can refer to this question for MySQL: https://stackoverflow.com/questions/17421448/query-to-remove-all-characters-after-last-comma-in-string — Muhammad Vakili
– Muhammad Vakili, Commented Jul 3, 2019 at 11:10

adambg · Accepted Answer · 2019-07-03 13:27:16Z

1

Just increase the index by the length of the substring you search, in your example 'Ltd.' is 4:

SELECT LEFT('Tracding Company Pvt Ltd. - Welecome Home', INSTR ('Tracding Company Pvt Ltd. - Welecome Home', 'Ltd.') + 4)

Another option is to use the CONCAT() function to add the missing value back:

SELECT CONCAT(SUBSTRING_INDEX('Tracding Company Pvt Ltd. - Welecome Home', 'Ltd.', 1), 'Ltd.')

EDIT: Corrected based on comments

edited Jul 3, 2019 at 13:27

answered Jul 3, 2019 at 11:34

adambg

3141 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Bohemian Over a year ago

Instead of 4 you could use length('Ltd.') in case the search string is a variable.

Vinod Over a year ago

@adambg, I am getting the result as only "4" but i need "Tracding Company Pvt Ltd."

adambg Over a year ago

Fixed the answer to better reflect the question

Collectives™ on Stack Overflow

Removing part of string after specific character using mysql

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related