asked
Find and replace values in a specific row
Hello, I need a SQL query in SQL Server in which to replace 1's to 9's. For example Category Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Orange 1 1 5 1 1 8 1 1 3 It would become Category Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Orange 9 9 5 9 9 8 9 9 3 Thanks for your help.
You have some good answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
select replace(Q1,'1','9') from table where category = 'Orange'
What version of SQL is this running? I have run it in MS SQL 2005 and SQL 2012 and it works for me.
I use this all the time in MS SQL Server 2005 to 2012. Here is the link to Microsoft's website with "Replace" syntax. Are your column names "Q1", "Q2", etc?
https://msdn.microsoft.com/en-us/library/ms186862%28v=sql.110%29.aspx
Try this, isn't gonna work: Create table #Table (Category Varchar(50),Q1 int, Q2 int, Q3 int, Q4 int, Q5 int, Q6 int, Q7 int, Q8 int, Q9 int ) GO INSERT INTO #Table Values ('Orange', 1, 1, 5, 1, 1, 8, 1, 1, 3 ) GO select replace(Q1,'1','9') from #table where category = 'Orange' GO select * from #Table Result: Category Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Orange 1 1 5 1 1 8 1 1 3
update YourTable set q1 = replace(q1, '1','9'), q2 = replace(q2, '1','9'), q3 = replace(q3, '1','9'), q4 = replace(q4, '1','9'), q5 = replace(q5, '1','9'), q6 = replace(q6, '1','9'), q7 = replace(q7, '1','9'), q8 = replace(q8, '1','9'), q9 = replace(q9, '1','9'), where Category = 'Orange'
Hi chawiro, It works great when the number of columns are not long. What happen if there are 50 columns or more. I do not want to write.. set q1 = replace(q1, '1','9'), set q2 = replace(q2, '1','9'), ................. set q50 = replace(q50, '1','9') where Category = 'Orange' Is there any better solution? Thanks
declare @cmd nvarchar(max) declare @columns bit = 0 set @cmd = 'update yourTable set' select @cmd = @cmd + ' ' + COLUMN_NAME + ' = REPLACE(' + COLUMN_NAME + ', ' + '''' + '1' + ''''+ ', ' + '''' + '9' + '''' + '),' from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'yourTable' and COLUMN_NAME != 'category' if @@ROWCOUNT > 0 set @columns = 1 IF RIGHT(@cmd, 1) = ',' set @cmd = substring(@cmd, 1, len(@cmd) - 1) if @columns = 1 exec (@cmd)
Here is another option using dynamic sql. You can change your @MyWhere to for the records you want to update by changing it. If you have Q1 to Q50, just change the "While @counter < 5" to "While @counter < 51". If you want to test syntax of dynamic sql, just rem out the exec statement and remove the -- in front of print statement to print to screen. --Populate Variables from Matrix Table DECLARE @counter INT SET @counter = 1 WHILE @counter < 5 BEGIN DECLARE @MyWhere VARCHAR(50), @mySQLCommand varchar(max) SET @MyWhere = 'Orange' --Dynamic SQL Script SELECT @mySQLCommand = 'UPDATE CatTest set Q'+cast(@Counter as varchar(1))+' = replace(Q' + cast(@Counter as varchar(1)) + ',''1'',''9'') FROM CatTest WHERE Category = ''' + @MyWhere +'''' --PRINT @mySQLCommand EXEC (@mySQLCommand) SET @counter = @counter + 1 END
Hi red68, I have this example with the following link.
http://sqlfiddle.com/#!3/a4c6f/1/0 Please place your query in the right section of this link and run it, so you can see the result. I cannot run your query in order to get the correct output. Thanks.
20 People are following this question.
