Ampersands in CHAR fields

[flapeyre]

I have a SP which is populating a table (LOINT_Master) from another (LTMSPF).

The problem is that when the field LTMSPF.LTXDS1 contains an ampersand ("&"), the ampersand is stripped out of the corresponding CHAR field in LOINT_Master (LOINT_Master.Property_Location).

LTMSPF was populated from as AS/400 file (same structure). The two fields are both defined as char(70), however, LTMSPF.LTXDS1 is not nullable, while LOINT_Master.Property_Location is nullable.

How can I preserve the ampersands?

Here is insert statement:

    INSERT INTO dbo.Loint_Master
        (
          Location_Cd
        , Customer_Number
        , Status_Code
        , Last_Name
        , First_Name
        , Spouse_First_Name
        , Acct_1_Company
        , Account_1_Prefix
        , Account_1
        , Acct_2_Company
        , Account_2_Prefix
        , Account_2
        , Acct_3_Company
        , Account_3_Prefix
        , Account_3
        , Acct_4_Company
        , Account_4_Prefix
        , Account_4
        , Acct_5_Company
        , Account_5_Prefix
        , Account_5
        , Account_1_Notes
        , Account_2_Notes
        , Account_3_Notes
        , Account_4_Notes
        , Account_5_Notes
        , Property_Type
        , Preneed_Filing
        , Interment_Flag
        , Infant_Of_Flag
        
       -- Property Location:
        , Property_Location
        , Update_Date
        , Misc_Flag_1
        , Misc_Flag_2
        , Update_User_ID
        )
        SELECT
            LTMSPF.Location_Cd
          , LTMSPF.LTXCST
          , LTMSPF.LTXXST
          , LTRIM(RTRIM(LTMSPF.LTXXLN))
          , LTRIM(RTRIM(LTMSPF.LTXXFN))
          , NULLIF(LTRIM(RTRIM(LTMSPF.LTXXSP)), '')
          , NULLIF(LTMSPF.LTANC1, 0)
          , NULLIF(LTRIM(RTRIM(LTMSPF.LTANP1)), '')
          , NULLIF(LTMSPF.LTXAN1, 0)
          , NULLIF(LTMSPF.LTANC2, 0)
          , NULLIF(LTRIM(RTRIM(LTMSPF.LTANP2)), '')
          , NULLIF(LTMSPF.LTXAN2, 0)
          , NULLIF(LTMSPF.LTANC3, 0)
          , NULLIF(LTRIM(RTRIM(LTMSPF.LTANP3)), '')
          , NULLIF(LTMSPF.LTXAN3, 0)
          , NULLIF(LTMSPF.LTANC4, 0)
          , NULLIF(LTRIM(RTRIM(LTMSPF.LTANP4)), '')
          , NULLIF(LTMSPF.LTXAN4, 0)
          , NULLIF(LTMSPF.LTANC5, 0)
          , NULLIF(LTRIM(RTRIM(LTMSPF.LTANP5)), '')
          , NULLIF(LTMSPF.LTXAN5, 0)
          , NULLIF(LTRIM(RTRIM(LTMSPF.LTANT1)), '')
          , NULLIF(LTRIM(RTRIM(LTMSPF.LTANT2)), '')
          , NULLIF(LTRIM(RTRIM(LTMSPF.LTANT3)), '')
          , NULLIF(LTRIM(RTRIM(LTMSPF.LTANT4)), '')
          , NULLIF(LTRIM(RTRIM(LTMSPF.LTANT5)), '')
          , NULLIF(LTRIM(RTRIM(LTMSPF.LTXXPT)), '')
          , NULLIF(LTRIM(RTRIM(LTMSPF.LTXPRF)), '')
          , NULLIF(LTRIM(RTRIM(LTMSPF.LTXINT)), '')
          , NULLIF(LTRIM(RTRIM(LTMSPF.LTXINF)), '')
          
          -- Property Location:
          , NULLIF(LTRIM(RTRIM(LTMSPF.LTXDS1)), '')
          , CASE ( ISDATE(CAST(LTMSPF.LTXXMM AS VARCHAR(2)) + '/'
                          + CAST(LTMSPF.LTXXDD AS VARCHAR(2)) + '/'
                          + CAST(LTMSPF.LTXXYR AS VARCHAR(4))) )
              WHEN 1
              THEN CAST(CAST(LTMSPF.LTXXMM AS VARCHAR(2)) + '/'
                   + CAST(LTMSPF.LTXXDD AS VARCHAR(2)) + '/'
                   + CAST(LTMSPF.LTXXYR AS VARCHAR(4)) AS DATETIME)
              ELSE CURRENT_TIMESTAMP
            END
          , NULLIF(LTRIM(RTRIM(LTMSPF.LTXXF1)), '')
          , NULLIF(LTRIM(RTRIM(LTMSPF.LTXXF2)), '')
          , NULLIF(LTRIM(RTRIM(LTMSPF.LTUSER)), '')
          FROM
            dbo.LTMSPF
          ORDER BY
            LTMSPF.Location_Cd
          , LTMSPF.LTXCST

Thanks in advance.

-- Francis
Et cognoscetis veritatem, et veritas liberabit vos.

 

[MarkSweetland]

I think the problem may lie in the way the AS/400 stores character information (EBCDIC). I guess it would depend on how the LTMSPF was populated, but you may have to scan the LTMSPF table update these fields. Or re-import with some conversion performed.


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[gmmastros]

Are you sure?

What I mean is... Have you checked the data in the table to see if the & are in there?

I've seen similar things happen in my own application, where the & appears to be missing, but it really isn't. For example, if I have a piece of data like '5th Street & Main St', and I load this data in to a Label control, the & appears to be missing. It's not really missing though. It's there. When you have a label control (or button) in VB6, you can set a hot key for it by using the &. FOr example, suppose you have a button with a caption of "Edit" but you want to user to be able to use ALT-E to effectively click the button. All you need to do is to set the caption property of the button to "&Edit". The & will cause the E (in Edit) to get underlined, and the ALT-E key to be the same as clicking the button.

In VB6, labels have another property named "UseMnemonic" that defaults to true. If you set this property to False, the & will display normally.

With a button, you need to double the & to make it display as a single &.

Of course, I have no idea if this is what's going on with your data, but it might be worth a look.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[flapeyre]

The & are in the first table. I checked.

I got to thinking, though. Maybe it's one of those functions (LTRIM, RTRIM, or NULLIF). So I decided to try this:

CREATE TABLE #temp1
  (
    Test_Col1 CHAR(72) NOT NULL
  )
CREATE TABLE #temp2 
  (
    Test_Col2 CHAR(72) NULL 
  )
INSERT INTO #temp1
    ( Test_Col1 )
  VALUES
    ( 'R&D' )
      
INSERT INTO #temp2
    ( Test_Col2 )
    (
      SELECT
          LTRIM(RTRIM(NULLIF(Test_Col1,'')))
        FROM
          #temp1
    )
    
SELECT
    t.Test_Col2
  FROM
    #temp2 t
/*
DROP TABLE #temp1
DROP TABLE #temp2

*/

Note the difference in how the functions are nested. That second SELECT does give me 'R&D'.

However, I changed
, NULLIF(LTRIM(RTRIM(LTMSPF.LTXDS1)), '')
to
, LTRIM(RTRIM(NULLIF(LTMSPF.LTXDS1,'')))

Unfortunately, the &s are missing again.

-- Francis
Et cognoscetis veritatem, et veritas liberabit vos.

 

[flapeyre]

There are ampersands in that field in the source file:

SELECT
    COUNT(*)
  FROM
    dbo.LTMSPF l
  WHERE
    l.LTXDS1 LIKE '%&%'

The result: 33797.

After recreating the SP, and re-running it, I tried this:

SELECT
    COUNT(*)
  FROM
    [Loint_Inquiry].[dbo].[Loint_Master]
  WHERE
    dbo.Loint_Master.Property_Location LIKE '%&%'

The result: Zero.

-- Francis
Et cognoscetis veritatem, et veritas liberabit vos.

 

[flapeyre]

Duh - I found the problem. It was in a function I had to convert to proper case:

-- clean up html tags that sometimes get into name/address data from web forms   
          --SET @str = REPLACE(@str,
          --                   ''','''')        set @str= replace(@str, ''',
          --                   '''')
          SET @str = REPLACE(@str, ''', '''')
          [COLOR=red]SET @str = REPLACE(@str, '&', '')[/color]
          SET @str = REPLACE(@str, '''', '''')
          SET @str = REPLACE(@str, ''', '''')

Commented that out, and all is good.

Thanks.

-- Francis
Et cognoscetis veritatem, et veritas liberabit vos.