CODEWITHSUNDEEP
t-sql
ETLUser
ETLUser

Reputation: 401

SQL Statement for removing letters from column values after special character

I have a Table named "TableA" and there is column " KNumber" and that column has some values [Data] which contain Pipe ' | ' in.

For example as below.

1. AMERIK20121218|HA

2. AMERIKINITIAL20121130|DSS

3. |DSS

4. |HI

Now I want to perform clean up on that "TableA" by removing these extra values in KNumber column- If there is an ' | ' at

the end and any value after ' | ', that need to be removed.

What would be the TSQL query to perform this operation? I have like 3000 rows for that column which have ' | ' in it. I want to update all these 3000 rows and remove whatever it is after that '|'.

I use SQL Server 2008 R2.

Thanks in advance for help.

Upvotes: 0

Views: 1755

Answers (1)

Alex Filipovici
Alex Filipovici

Reputation: 32561

If the KNumber is in a full text index, you may use:

UPDATE TableA
SET KNumber = SUBSTRING(KNumber,0,CHARINDEX('|', KNumber)-1)
WHERE Contains(KNumber, '|')

If it's not, you may use:

UPDATE TableA
SET KNumber = SUBSTRING(KNumber,0,CHARINDEX('|', KNumber)-1)
WHERE KNumber LIKE '%|%'

Here's a demo.

Upvotes: 1

Related Questions