How to remove everything after certain character in SQL?

Question

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 1

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

Answer 2

You can use SQL Trim() function

SELECT TRIM(TRAILING '-' FROM BHEXLIVESQLVS1-LIVE61MSSQL)
AS TRAILING_TRIM
FROM table;

The result should be "BHEXLIVESQLVS1"

Answer 3

select SUBSTRING(col_name,0,Charindex ('-',col_name))

Answer 4

Assuming you need to do this in a query, you can use the string functions of your database. For DB2 this would look something like

select SUBSTR(YOURCOLUMN, 1, LOCATE('-',YOURCOLUMN)) from YOURTABLE where ...

In SQL Server you could use

SUBSTRING

and

CHARINDEX

Answer 5

For SQL server you can do this,

LEFT(columnName, charindex('-', columnName)) to remove every character after '-'

to remove the special character as well do this,

LEFT(columnName, charindex('-', columnName)-1)

Answer 6

SELECT SUBSTRING(col_name,0,Charindex ('-',col_name)) FROM table_name WHERE col_name='yourvalue'

Eg.

SELECT SUBSTRING(TPBS_Path,0,Charindex ('->',TPBS_Path)) FROM [CFG].[CFG_T_Project_Breakdown_Structure] WHERE TPBS_Parent_PBS_Code='LE180404'

here TPBS_Path is the column for which trim is to be done and [CFG].[CFG_T_Project_Breakdown_Structure] is table name and TPBS_Parent_PBS_Code='LE180404' is the select condition. Everything after '->' will be trimmed