Searching all the columns/rows of a table for a particular value and replacing it with another

  • Hi,

    I'm wondering how you search all the columns of a particular table for a value. We received data that was coded '99999' for all nulls; I need to replace all instances of '99999' with NULL.

    It seems like

    SELECT * FROM {table x} Where {some variable for value}=99999 should do the trick.

    But I haven't been able to find any built in variable for a column's value. I HAVE found some stored procedures out there. Is it necessary to write a stored procedure for this?

    Also, I'm sure this is a really newbie question, but is using SELECT * bad? If so, is it a question of query optimization?

    Thanks,

    Ron

  • rsteckly (11/23/2009)


    Hi,

    I'm wondering how you search all the columns of a particular table for a value. We received data that was coded '99999' for all nulls; I need to replace all instances of '99999' with NULL.

    It seems like

    SELECT * FROM {table x} Where {some variable for value}=99999 should do the trick.

    But I haven't been able to find any built in variable for a column's value. I HAVE found some stored procedures out there. Is it necessary to write a stored procedure for this?

    Also, I'm sure this is a really newbie question, but is using SELECT * bad? If so, is it a question of

    query optimization?

    Thanks,

    Ron

    It is not required to write a stored procedure for doing this.

    If replacing is all what you need, then use this query.

    UPDATE TABLE SET COLUMN = NULL WHERE COLUMN = '99999'

    I could not understand what you meant by stored procedures out there.


    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • Well, granted this is different in that it returns the column names of those containing the queried value. I found it here: http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/the-ten-most-asked-sql-server-questions--1#2

    Here goes:

    CREATE PROCEDURE FindMyData_Number

    @DataToFind NVARCHAR(4000),

    @ExactMatch BIT = 0

    AS

    SET NOCOUNT ON

    DECLARE @Temp TABLE(RowId INT IDENTITY(1,1), TableName sysname, ColumnName SysName, DataType VARCHAR(100), DataFound BIT)

    DECLARE @IsNumber BIT

    DECLARE @ISDATE BIT

    IF ISNUMERIC(CONVERT(VARCHAR(20), @DataToFind)) = 1

    SET @IsNumber = 1

    ELSE

    SET @IsNumber = 0

    INSERT INTO @Temp(TableName, ColumnName, DataType)

    SELECT C.Table_Name, C.Column_Name, C.Data_Type

    FROM Information_Schema.Columns AS C

    INNER Join Information_Schema.Tables AS T

    ON C.Table_Name = T.Table_Name

    WHERE Table_Type = 'Base Table'

    And Data_Type In ('float','real','decimal','money','smallmoney','bigint','int','smallint','tinyint','bit')

    DECLARE @i INT

    DECLARE @max-2 INT

    DECLARE @TableName sysname

    DECLARE @ColumnName sysname

    DECLARE @SQL NVARCHAR(4000)

    DECLARE @PARAMETERS NVARCHAR(4000)

    DECLARE @DataExists BIT

    DECLARE @SQLTemplate NVARCHAR(4000)

    SELECT @SQLTemplate = CASE WHEN @ExactMatch = 1

    THEN 'If Exists(Select *

    From [ReplaceTableName]

    Where Convert(VarChar(40), [ReplaceColumnName])

    = ''' + @DataToFind + '''

    )

    Set @DataExists = 1

    Else

    Set @DataExists = 0'

    ELSE 'If Exists(Select *

    From [ReplaceTableName]

    Where Convert(VarChar(40), [ReplaceColumnName])

    Like ''%' + @DataToFind + '%''

    )

    Set @DataExists = 1

    Else

    Set @DataExists = 0'

    END,

    @PARAMETERS = '@DataExists Bit OUTPUT',

    @i = 1

    SELECT @i = 1, @max-2 = MAX(RowId)

    FROM @Temp

    WHILE @i <= @max-2

    BEGIN

    SELECT @SQL = REPLACE(REPLACE(@SQLTemplate, 'ReplaceTableName', TableName), 'ReplaceColumnName', ColumnName)

    FROM @Temp

    WHERE RowId = @i

    PRINT @SQL

    EXEC SP_EXECUTESQL @SQL, @PARAMETERS, @DataExists = @DataExists OUTPUT

    IF @DataExists =1

    UPDATE @Temp SET DataFound = 1 WHERE RowId = @i

    SET @i = @i + 1

    END

    SELECT TableName, ColumnName

    FROM @Temp

    WHERE DataFound = 1

    go

  • Well, I am sorry for the code that I posted in the previous reply, it does not solve the entire problem. I did not see that you need to search all columns in a table until you posted the link (to less than dot Blog) to the stored proc.

    Your select query has aggravated my confusion......:-)


    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

    The site has a script and a discussion about it.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply