CODEWITHSUNDEEP
sqlsql-servert-sql
Michael Cabus
Michael Cabus

Reputation: 21

Removing extraneous characters in column using T-SQL

I am attempting to remove extraneous characters from data in a primary key column..the data in this column serves as a control number, and the extra characters are preventing a Web application from effectively interacting with the data.

As an example, one row may look like this:

ocm03204415 820302

I want to remove everything after the space...so the characters '820302'. I could manually do it, but, there are around 2,000 records that have these extra values in the column. It would be great if I could remove them programmatically. I can't do a simple Replace because the characters have no pattern...I couldn't define a rule to discover them...the only thing uniform is the space...although, now that I look at the data set, they do all start with 8.

Is there a way I could remove these characters programmatically? I am familiar with PL/SQL in the Oracle environment, and was wondering if Transactional SQL would offer some possibilities in the MS-SQL environment?

Thanks so much.

Upvotes: 2

Views: 1113

Answers (2)

Lamak
Lamak

Reputation: 70638

Try this:

UPDATE YourTable
SET YourColumn = LEFT(YourColumn,CHARINDEX(' ',YourColumn)-1)
WHERE CHARINDEX(' ',YourColumn) > 1

Upvotes: 3

mellamokb
mellamokb

Reputation: 56769

You may want to look into the CHARINDEX function to find the space. Then you can use SUBSTRING to grab everything up to the space in a single UPDATE statement.

Upvotes: 4

Related Questions