CODEWITHSUNDEEP
sqlsql-servervarchar
JTunney
JTunney

Reputation: 904

MSSQL - Remove all text after a '-' in a varchar field

I have a field in one of my tables that has names in it such as:
Category1 - Label1,
Category2 - Label2,
Category3 - Label3 and so on.

I created another field and I want to do an UPDATE that will copy all values over to the new field but so they appear as:
Category1
Category2
Category3

This would mean removing everything from the space before the hyphen to the right.

Note: There will ever only be one hyphen in the string to separate Category from Label.

Upvotes: 0

Views: 102

Answers (2)

HaveNoDisplayName
HaveNoDisplayName

Reputation: 8497

To achieve what you want, you have to select sub-string from Left using CHARINDEX() and LEFT() sql function. Then set Update new value in new column

UPDATE tblName
SET updatedColumn = CASE WHEN CHARINDEX('-', columnName) > 0 THEN
                         RTRIM(LEFT(columnName, CHARINDEX('-', columnName)-1))
                    ELSE columnName
                    END

Upvotes: 1

juergen d
juergen d

Reputation: 204756

update your_table
set col_new = case when charindex('-', col) > 0 
                   then RTRIM(left(col, charindex('-', col) - 1))
                   else col
              end

Upvotes: 3

Related Questions