Search code examples
sqlreplacessistrim

SQL Query to find and remove characters

I am importing data from a flat file to a SQL table called TCVS_tmp_PO_Detail using SSIS and SQL. Now and then I get some characters in the Purchase Order column that I would like to find and eliminate if they occur.

The characters are , so could I trim these 3 characters on the left of the number out? It does not occur all the time so I can run it on the front end of my SSIS job as a query to correct it before exporting it.

Here is an example of what it looks like when it occurs

Purchase Order
7
7
8
8
8
8
8
8
9
10
10
10
10
11
12
13
11
12
13
14
14
15
15
16
16
17
19
18
19
20
22
Solution

  • I'm not sure where the data lies for the processing, but you can easily do this with a case expression:

     (case when purchase_order like '%' then stuff(purchase_order, 1, 3, '')
       else purchase_order
  end) as purchase_order