Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2003
    Posts
    3

    Unanswered: How can I change the wrong inderted data

    I wish someone can help in this.

    One column in the table is varchar(64), normally it should contain data in the form
    xxxxxxx/yyyyyyy
    where xxxxx and yyyyyyy are numbers

    A new software is inserting the data with following format
    xxxxxxx/yyyyyyy/xxxxxxx/yyyyyyy

    This causing problem for the program to read this data and it will be hard to change its code.

    What can be the best way to control that from within the SQL?

    All helps would be appreciated

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Step 1: Fire software vendor that updated the code in a stupid way.

    Here is one way to extract the data. You should be able to modify this a littel to get the removal of bad data.


    create table test1
    (id int identity(1, 1),
    col1 varchar(20))

    insert into test1 (col1) values ('123/456/789/000')
    insert into test1 (col1) values ('123/456/000/789')
    insert into test1 (col1) values ('123456789/456/7/000')
    insert into test1 (col1) values ('123/456')

    select substring (col1, charindex ('/', col1, charindex ('/', col1) + 1)+ 1, 20)
    from test1
    where col1 like '%/%/%/%'

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I came up with:

    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99 (Col1 int IDENTITY, Col2 varchar(50))
    GO
    
    INSERT INTO myTable99 (Col2)
    SELECT '1111111/2222222' UNION ALL
    SELECT '3333333/4444444' UNION ALL
    SELECT '5555555/6666666' UNION ALL
    SELECT '7777777/8888888/9999999/0000000'
    GO
    
     
       SELECT Col2
         FROM myTable99
        WHERE LEN(Col2) = LEN(REPLACE(Col2,'/','')) + 1
    UNION ALL
       SELECT SUBSTRING(Col2,1,LEN(Col2)-CHARINDEX('/',Col2,(CHARINDEX('/',Col2)+1)))
         FROM myTable99
        WHERE LEN(Col2) = LEN(REPLACE(Col2,'/','')) + 3
    UNION ALL
       SELECT SUBSTRING(Col2,LEN(Col2)-CHARINDEX('/',Col2,(CHARINDEX('/',Col2)+1))+2,
    	      LEN(Col2)-(LEN(Col2)-CHARINDEX('/',Col2,(CHARINDEX('/',Col2)+1))+1))
         FROM myTable99
        WHERE LEN(Col2) = LEN(REPLACE(Col2,'/','')) + 3
    GO
    
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What do you mean by "control"? Do you mean fix the existing data, or do you want to prevent more bad data from being entered?

    And what do you want to do with the bad data? You can exclude the entire row, or accept only the valid characters, or (more complicated) split the value into two records?

    blindman

  5. #5
    Join Date
    Nov 2003
    Posts
    3
    The bad data will keep coming always, I just want to put somthing to reshape the data and enter them in correct way

    thanks for all

    Originally posted by blindman
    What do you mean by "control"? Do you mean fix the existing data, or do you want to prevent more bad data from being entered?

    And what do you want to do with the bad data? You can exclude the entire row, or accept only the valid characters, or (more complicated) split the value into two records?

    blindman

  6. #6
    Join Date
    Nov 2003
    Posts
    3

    Cool Thanks for the help

    Thanks all for the help, regards,

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What you need is a trigger on your table that automatically verfies and modifies the data as it is entered, using logic like that suggested by brett and MCrowley.

    Do you know how to write a trigger?

    blindman

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well, if the data is required to be in that format, then you can mess with them...

    Code:
    CREATE TABLE myTable99 (Col1 int IDENTITY, Col2 varchar(64) CHECK (LEN(COL2)-LEN(REPLACE(Col2,'/','')) = 1))
    GO
    
    INSERT INTO myTable99 (Col2)
    SELECT '1111111/2222222' UNION ALL
    SELECT '3333333/4444444' UNION ALL
    SELECT '5555555/6666666'
    GO
    
    INSERT INTO myTable99 (Col2)
    SELECT '7777777/8888888/9999999/0000000'
    GO
    
       SELECT Col2
         FROM myTable99
    GO
    
    DROP TABLE myTable99
    GO
    I would stay away from the trigger...

    But you may not have a choice, but to use one...

    What is this table used for?

    EDIT: How does the data get in? OLTP or Loads?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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