Professional Web Applications Themes

Query to identify and change uppercase data - Coldfusion Database Access

Hi All, Due to an import error I have a table field that holds a piece of data such as this: STRIEFPRFL Striking Effects Promotions 2095 Bay Road This field should not have the all uppercase company code at the beginning...(or the company name for that matter but I think I've got that removed) it should only have the Street address. Anyone have a good query that would enable me to say: "look at 'fieldname' and identify the first instance of x number of characters or greater that are all in uppercase and delete them" or something similar? In the ...

Sponsored Links
  1. #1

    Default Query to identify and change uppercase data

    Hi All,

    Due to an import error I have a table field that holds a piece of data such as
    this:

    STRIEFPRFL Striking Effects Promotions 2095 Bay Road

    This field should not have the all uppercase company code at the
    beginning...(or the company name for that matter but I think I've got that
    removed) it should only have the Street address.

    Anyone have a good query that would enable me to say:

    "look at 'fieldname' and identify the first instance of x number of characters
    or greater that are all in uppercase and delete them" or something similar? In
    the example above the uppercase company code has 10 characters but I need to be
    able to grab ones that will be a minimum of 6 but as high as 15. There are
    some that are shorter than 6 but they are few so we could clean those by hand
    if needed.

    Any thoughts?
    Thx,
    Mike

    Sponsored Links
    MikeyJ Guest

  2. #2

    Default Re: Query to identify and change uppercase data

    Since you said field instead of column I will assume you are working on a DB2
    file instead of a table. The following only is testing the first set of
    characters before the first space. First to see if it is upper case and then
    if it is numeric. Your database may use UCASE instead of UPPER. It may
    support LOCATE or INSTR instead of POSSTR and the order and number of arguments
    may vary. It may have a different order for the arguements of TRANSLATE or
    XLATE. You may need to have a different test for nulls other than using VALUE.
    The following may have a problem if there isn't atleast one space in the
    field. I only did the select because you need to be sure that the data looks
    OK before you do the update.



    select field,
    case when upper(substr(field,1,posstr(field,' ')-1) )
    = (substr(field,1,posstr(field,' ')-1))
    and trim(translate(substr(field,1,posstr(field,' ')-1),
    ' ','1234567890')) <> ''
    then (substr(field,posstr(field,' ')+1,
    length(field) - posstr(field,' ')))
    else field end as x
    from myfile
    and trim(value(field,'')) <> ''

    draves Guest

  3. #3

    Default Re: Query to identify and change uppercase data

    Thanks for the reply!

    I was silly in not specifying that the db is MS SQLExpress 2005 and that it IS
    a table so column would be the proper terminology so forgive me! Does your
    theory still stand or does your code need some modification based on this info?

    Thx!
    Mike

    MikeyJ Guest

  4. #4

    Default Re: Query to identify and change uppercase data

    Based on looking at the Transact-SQL Reference at
    http://msdn2.microsoft.com/en-us/library/ms166026.aspx

    select column,
    case when upper(substring(column,1,charindex(' ',column)-1) )
    = (substring(column,1,charindex(' ',column)-1))
    and isnumeric(substring(column,1,charindex(' ',column)-1)) <> 1
    then (substring(column,charindex(' ',column)+1,
    len(column) - charindex(' ',column)))
    else column end as x
    from mytable
    and trim(isnull(column,'')) <> ''

    draves Guest

  5. #5

    Default Re: Query to identify and change uppercase data

    Thanks so much for your work! This happens to be in a database that I'm not
    familiar with...it's part of our 3rd party CRM app and I didn't realize until I
    got an error and went and checked, that the column we're trying to work with is
    an NTEXT field. At the least, the charindex method won't work with an NTEXT
    field. ;-(

    So, I attempted using CAST to change it to NVARCHAR but I get other errors
    like "Invalid length parameter passed to the SUBSTRING function" so I'm not
    sure where to go now?!

    Thx,
    Mike

    MikeyJ Guest

  6. #6

    Default Re: Query to identify and change uppercase data

    In addition to doing the CASTing add the following to the where clause:

    and charindex(' ',cast(column as nvarchar(max) )) > 1

    draves Guest

Similar Threads

  1. CF Query Column Names Returned in UPPERCASE
    By ericbelair in forum Macromedia Flex General Discussion
    Replies: 1
    Last Post: April 30th, 02:41 PM
  2. Creating data query from mySQL stored query!?
    By johnegbert in forum Coldfusion Database Access
    Replies: 2
    Last Post: August 5th, 09:28 PM
  3. Identify Application change
    By bal_logicmanager in forum Macromedia Flex General Discussion
    Replies: 1
    Last Post: April 3rd, 09:43 PM
  4. Query of Query Missing Some Data
    By g1zm0guy in forum Coldfusion Database Access
    Replies: 7
    Last Post: January 17th, 09:30 AM
  5. Selection change query
    By James168 in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: May 4th, 01:46 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139