Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2004
    Posts
    31

    Unanswered: deleting rows based on nvarchar data

    I have a table that contains rows that I would like to delete based on a field and it's contents.

    What is the correct syntax to script the removal of these rows based field parameter?

  2. #2
    Join Date
    Mar 2004
    Location
    West London
    Posts
    34

    Cool

    delete tableA where fieldB = ?

    Is that what you mean?

  3. #3
    Join Date
    Apr 2004
    Posts
    31
    Kinda. I only have one table and want to delete specific rows from that table that have a specific data within a certain field.

    Let be more specific. I have a table (tableA) with 10 fields. Field 3 has data that does not conform to a datetime format and I would like to remove it. The field is currently a nvarchar(50) type (2003-10-10).

    I want to remove rows that contain data that is looks like this
    (0020-10-10). Make sense?

  4. #4
    Join Date
    Mar 2004
    Location
    West London
    Posts
    34
    delete from table where field3 ='0020-10-10'

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Perhaps you can use the ISDATE() function, which returns 1 if a string can be converted to a valid date, and 0 if it cannot.

    Try this query:

    select *
    from YourTable
    where ISDATE([Column3]) = 0

    If this returns the rows you want deleted, then change the query to a delete query:

    delete
    from YourTable
    where ISDATE([Column3]) = 0
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Apr 2004
    Posts
    31
    right but I forgot to mention, there are all kinds of variation of that date.

    I ran a script that reads as follows to help identify data within a field that does not fit a date format->

    SELECT * FROM findet WHERE ISDATE(servfrom) = 0

    This gave me a list of records that are not in proper date format. Now, I would like to remove them from my table. Can I use the same,

    delete findet where ISDATE(servfrom)=0

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    See previous post.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Apr 2004
    Posts
    31
    That worked like a charm, thank you!!

Posting Permissions

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