How to remove everything after certain character in SQL?

removeall
user3446325 picture user3446325 · Mar 21, 2014 · Viewed 8.6k times · Source

I've got a list 400 rows +. Each row looks similar to this: example-example123 I would like to remove everything past '-' so that I'm left with just the beginning part: example123 Any help would be greatly appreciated.

Answer

CodeBird picture CodeBird · Mar 21, 2014

try it like this:

UPDATE table SET column_name=LEFT(column_name, INSTR(column_name, '-')-1) 
WHERE INSTR(column_name, '-')>0;

If you only want to select you do it this way:

SELECT LEFT(column_name, INSTR(column_name, '-')-1) FROM table;

INSTR function gets you the position of your - then you update the column value to become from the first letter of the string till the position of the - -1

Here's a fiddle

Related questions

Remove empty strings from array while keeping record Without Loop?

This question was asked here: Remove empty strings from array while keeping record of indexes with non empty strings If you'd notice the given as @Baz layed it out; "I", "am", "", "still", "here", "", "man" "and from this I wish to …

Read More
Java: removing numeric values from string

I have suceeded with the help of this community in removing numeric values from user input, however, my code below will only retrieve the alpha characters before the numeric that has been removed: import java.util.Scanner; public class Assignment2_…

Read More
Remove specific char from String

How To remove a specific Character from a String. I have a Arraylist testingarray. String line=testingarray.get(index).toString(); I want to remove a specific character from line. I have Array of uniCodes int uniCode[]={1611,1614,1615,1616,1617,1618}; i want to remove …

Read More