CODEWITHSUNDEEP
sql-serversql-server-2008-r2
Rman Edv
Rman Edv

Reputation: 161

Removing Text after a combination of characters in SQL Server 2008 R2

I have a Column including values like this :

         12B2_10
         12C2_20

I want to delete what is written after _1 or _2 My expected Result is:

         12B2_1
         12C2_2

format of column is nvarchar(50)

I tried this:

         update table  
         set column= ( SELECT SUBSTRING(column,1,CHARINDEX('_',column)+1),[ID] from table as z where z.ID=table.SID )

Thank you

Upvotes: 0

Views: 48

Answers (2)

Jesuraja
Jesuraja

Reputation: 3844

You can try this also

UPDATE table
SET    column = LEFT(column, CHARINDEX('_', column) + 1)

Upvotes: 2

mehdi lotfi
mehdi lotfi

Reputation: 11601

Try this:

SELECT SUBSTRING(YourColumn,1,CHARINDEX('_',YourColumn)+1) 
FROM YourTable

In order to update your table use following query:

Update YourTable
Set YourColumn = SUBSTRING(YourColumn,1,CHARINDEX('_',YourColumn)+1)

Upvotes: 2

Related Questions