TSQL CASE, CHECK DATA, CONVERT, TRIM
Posted on 2010-08-12
How do you compare & replace in TSQL while converting and trimiming? I am in the midst of a vary large migration conversion between and older table with different formatting and rules on the data from the target output. Here is an example:
IF OBJECT_ID('TEMPDB..#LTS_FIX') IS NOT NULL
BEGIN DROP TABLE #LTS_FIX END
....(VARIOUS CONVERT DATA ELEMENT STATEMENTS)...
LOAN_NUMBER 0123456789 MORTGAGOR_LAST_NAME: OSHEA
LOAN_NUMBER 0234567891 MORTGAGOR_LAST_NAME: OCONNELL
LOAN_NUMBER 0123456789 MORTGAGOR_LAST_NAME: O'SHEA
LOAN_NUMBER 0234567891 MORTGAGOR_LAST_NAME: O'CONNELL
In addition to this issue the old data is not left adjusted, rather it is free form and sometimes the MORTGAGOR_LAST_NAME is missing.
Here is the code I have so far:
,CASE WHEN (cMortgLastName IS NOT NULL) AND (cMortgLastName <> ' ')
THEN CONVERT(VARCHAR(10), nullif(LTRIM(RTRIM(cMortgLastName)),'')) END as MORTGAGOR_LAST_NAME
.. MORE DATA ELEMENT CONVERT STATEMENT..
I am thinking if I do an INNER JOIN on the from statement like this:
INNER JOIN dbo.BDES_MAP
The BDES_MAP table contains new data with the correct format. Then I will of course of the same field element in the new table to match against the old.
I need to keep what I have and add to to the same statement the ability to check & replace the old input data against new data with the proper format, i.e, an apostrophe where needed.
Help is most appreciated!