Solved

TSQL CASE, CHECK DATA, CONVERT, TRIM

Posted on 2010-08-12
4
817 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:ScottPletcher
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SLQ View not updating 10 47
SQL 2012 Syntax Error 5 24
sql query 7 35
Calculating Business Hours 19 65
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

757 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

20 Experts available now in Live!

Get 1:1 Help Now