Solved

TSQL CASE, CHECK DATA, CONVERT, TRIM

Posted on 2010-08-12
4
822 Views
Last Modified: 2012-06-27
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
SELECT

....(VARIOUS CONVERT DATA ELEMENT STATEMENTS)...
Old data:  
LOAN_NUMBER 0123456789   MORTGAGOR_LAST_NAME: OSHEA
LOAN_NUMBER 0234567891   MORTGAGOR_LAST_NAME: OCONNELL

NEW DATA:
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..

INTO #LTS_FIX
FROM      LTSArchive.dbo.LoanMaster_0609  
 
I am thinking if I do an INNER JOIN on the from statement like this:

INTO #LTS_FIX
FROM      LTSArchive.dbo.LoanMaster_0609  
  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!
 


0
Comment
Question by:Leogal
  • 2
4 Comments
 

Author Comment

by:Leogal
ID: 33422318
Still researching and trying - here is the code I have now in the select statement:
             
    ,CASE  WHEN (cMortgLastName IS NOT NULL) AND (cMortgLastName <> ' ')
                THEN CONVERT(VARCHAR(10), nullif(LTRIM(RTRIM(cMortgLastName)),''))
                  ELSE REPLACE('cMortgLastName','cMortgLasttName','B.MORTGAGOR_LAST_NAME')
                  END as  MORTGAGOR_LAST_NAME

I think if I can get the join to work, then I may have it.

Here is the join:
INTO #LTS_FIX
FROM      LTSArchive.dbo.LoanMaster_0609  
  INNER JOIN dbo.BDES_MAP B

here is the error returned when parse is performed:
Msg 102, Level 15, State 1, Line 212
Incorrect syntax near 'B'.
0
 

Author Comment

by:Leogal
ID: 33423387
Okay I modified the select code to look like this:
select
,field,
,field,
more fields,

    ,CASE  WHEN (cMortgLastName IS NOT NULL) AND (cMortgLastName <> ' ') AND (cMortgLastName = B.MORTGAGOR_LAST_NAME)
                THEN CONVERT(VARCHAR(10), nullif(LTRIM(RTRIM(cMortgLastName)),''))
                  ELSE REPLACE('cMortgLastName','cMortgLasttName','B.MORTGAGOR_LAST_NAME')
                  END as  MORTGAGOR_LAST_NAME  
   ,field
  ,field,
more fields...
INTO      #LTS_MAP  
FROM      LTSArchive.dbo.LoanMaster_0609  L
 INNER JOIN TEST.dbo.bdes_MAP B on (L.cLoanNo = B.LOAN_NUMBER)    

no errors, just got something fouled with the replace as the field name  from the old table is replacing the MORTGAGOR_LAST_NAME as opposed to populating with the new field data
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 125 total points
ID: 33423555
Leogal:

FYI, when you do an equality test using = against a column like cMortgLastName then NULLs will be not be matched therefore :

cMortgLastName <>  ' '

Will be a sufficient first test. Similarly, if you don't need to worry about when both cMortgLastName and MORTGAGOR_LAST_NAME is '' then you can simply use the direct comparison of the two columns.

As far as the REPLACE syntax, it is usually REPLACE(fieldnameOrText, originaltext, replacementtext).
e.g., REPLACE('ABCDEF', 'A', '1') will result in '1BCDEF'

REPLACE('cMortgLastName','cMortgLasttName','B.MORTGAGOR_LAST_NAME')

Seems odd for a number of reasons as you would not put column names in ' so remove those and then second if you are simply replacing the entire contents of one field with the other then simply use the other field.

And in fact, if the only time you are using the column is if the two fields are equal, you can just do trim then just do this:

NULLIF(RTRIM(LTRIM(B.MORTGAGOR_LAST_NAME)), '') as  MORTGAGOR_LAST_NAME  

HTH,

Kevin
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 33423694
I don't fully understand exactly what you're trying to do.

Please post table defs / sample *full* rows for each of the "before" and "after" tables, identifying the link column(s) to the other table you mentioned.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL server 2008 and after encryption method 32 44
query execution hang 5 28
MS SQL with ODBC 5 34
SQL Insert parts by customer 12 31
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now