0

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.

jeffjenx
  • 17,041
  • 6
  • 57
  • 99
Vinod
  • 3
  • 2
  • 2
    Possible duplicate of [SQL Server replace, remove all after certain character](https://stackoverflow.com/questions/1668014/sql-server-replace-remove-all-after-certain-character) – Muhammad Vakili Jul 03 '19 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 Jul 03 '19 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](https://stackoverflow.com/questions/17421448/query-to-remove-all-characters-after-last-comma-in-string) – Muhammad Vakili Jul 03 '19 at 11:10

1 Answers1

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

adambg
  • 294
  • 1
  • 5