Solved

TSQL CASE, CHECK DATA, CONVERT, TRIM

Posted on 2010-08-12
4
829 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 60

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

Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

Question has a verified solution.

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

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

737 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