CODEWITHSUNDEEP
mysqlstringtrim
Satish
Satish

Reputation: 752

Generic MySQL Query for Trimming Column Data

I have a Table in which one of the column contains data as follows:

file1 (1.1.1.1).txt
file2 (1.1.1.2).txt
file3 (1.1.1.3).txt
file4 (1.1.1.4).txt

I want to trim off the text from Opening parenthesis. After trimming, the Updated data should be as follows:

file1
file2
file3
file4

Since the Records are in Hundreds, is there a Generic way to Trim off the unwanted text? How the Query is written in MySQL

Upvotes: 0

Views: 92

Answers (2)

Casey
Casey

Reputation: 309

Locate the parentheses, minus 2 positions should give you what you want.

MySQL

UPDATE your-table
    SET your-column = LEFT(your-column, LOCATE('(', your-column, 1)-2)

Same premise with Oracle syntax 

UPDATE your-table
    SET your-column = SUBSTR(your-column, 1, INSTR(your-column, '(') - 2);

Upvotes: 1

Peter Chaula
Peter Chaula

Reputation: 3711

Your SQL query could be like this:

         UPDATE your_table_here SET file_name = RTRIM(SUBSTRING(file_name, 1, LOCATE('(', file_name)) - 1 );

Just replace file_name and your_table

Upvotes: 1

Related Questions