For the last few weeks, I have been doing Friday Puzzles and I am really loving it. Yesterday I received a very interesting question by Navneet Chaurasia on Facebook Page. He was asked this question in one of the interview questions for job. Please read the original thread for a complete idea of the conversation. I am presenting the same question here.
Puzzle
Let us assume there is a single column in the table called Gender. The challenge is to write a single update statement which will flip or swap the value in the column. For example if the value in the gender column is ‘male’ swap it with ‘female’ and if the value is ‘female’ swap it with ‘male’.
Here is the quick setup script for the puzzle.
USE tempdb
GO
CREATE TABLE SimpleTable (ID INT, Gender VARCHAR(10))
GO
INSERT INTO SimpleTable (ID, Gender)
SELECT 1, 'female'
UNION ALL
SELECT 2, 'male'
UNION ALL
SELECT 3, 'male'
GO
SELECT *
FROM SimpleTable
GO
The above query will return following result set.
The puzzle was to write a single update column which will generate following result set.
There are multiple answers to this simple puzzle. Let me show you three different ways. I am assuming that the column will have either value ‘male’ or ‘female’ only.
Method 1: Using CASE Statement
I believe this is going to be the most popular solution as we are all familiar with CASE Statement.
UPDATE SimpleTable
SET Gender = CASE Gender WHEN 'male' THEN 'female' ELSE 'male' END
GO
SELECT *
FROM SimpleTable
GO
Method 2: Using REPLACE Function
I totally understand it is the not cleanest solution but it will for sure work in giving situation.
UPDATE SimpleTable
SET Gender = REPLACE(('fe'+Gender),'fefe','')
GO
SELECT *
FROM SimpleTable
GO
Method 3: Using IIF in SQL Server 2012
If you are using SQL Server 2012 you can use IIF and get the same effect as CASE statement.
UPDATE SimpleTable
SET Gender = IIF(Gender = 'male', 'female', 'male')
GO
SELECT *
FROM SimpleTable
GO
You can read my article series on SQL Server 2012 various functions over here.
- SQL SERVER – Denali – Logical Function – IIF() – A Quick Introduction
- SQL SERVER – Detecting Leap Year in T-SQL using SQL Server 2012 – IIF, EOMONTH and CONCAT Function
Let us clean up.
DROP TABLE SimpleTable
GO
Question to you:
I came up with three simple tricks where there is a single UPDATE statement which swaps the values in the column. Do you know any other simple trick? If yes, please post here in the comments. I will pick two random winners from all the valid answers. Winners will get 1) Print Copy of SQL Server Interview Questions and Answers 2) Free Learning Code for Online Video Courses
I will announce the winners on coming Monday.
Reference: Pinal Dave (https://blog.sqlauthority.com)
78 Comments. Leave new
Awesome
I find this to be a rather pleasing solution, easily adaptable to more values and the quickest of my own variants for a few hundred thousand rows in a heap.
UPDATE SimpleTable
SET Gender=(SELECT ‘male’ WHERE Gender=’female’ UNION ALL SELECT ‘female’ WHERE Gender=’male’)
This is cool. Similar to my approach proposed above except that you are not using colaesce and also you are not using a from clause (which I forgot is possible in SQL Server thanks to my experience in Oracle :-))…
I like this approach and it was the quickest of my attempts:
UPDATE SimpleTable
SET Gender=(SELECT ‘male’ WHERE Gender=’female’ UNION ALL SELECT ‘female’ WHERE Gender=’male’)
Another version (but slower) that I like stylistically is this:
UPDATE SimpleTable
SET Gender=X.NewGender
FROM (VALUES(‘male’,’female’),(‘female’,’male’)) AS X(OldGender,NewGender)
WHERE SimpleTable.Gender=X.OldGender
The second version looks cooler :-)
/*
Another Approach: WIthout using replace. And using sheer mathematical approach.
*/
create table #temp(id int, datacolumn varchar(6))
insert into #temp
values (1,’gent’),(2,’female’),(3,’gent’)
select * from #temp
declare @value1 char(4), @value2 char(6),@temp char(16)
set @value1 = ‘gent’
set @value2 = ‘female’
set @temp = @value2 + @value1 + @value2
/*
The below substring call calculates the replace string dynamically without any hard-coded indexes or lengths
*/
update #temp
set datacolumn = SUBSTRING(@temp,CHARINDEX(datacolumn,@temp,1) + LEN(datacolumn),LEN(@value1 + @value2) – LEN(datacolumn))
select * from #temp
–Solution : 1
DECLARE @TAB TABLE (ID INT, Gender VARCHAR(10))
INSERT INTO @TAB
SELECT 1, ‘female’
UNION ALL SELECT 2, ‘male’
UNION ALL SELECT 3, ‘male’
DECLARE @list VARCHAR(100)
SET @list = (SELECT DISTINCT ‘,’ + Gender FROM @TAB FOR XML PATH(”))
SELECT ID, REPLACE(REPLACE(@list, ‘,’ + Gender, ”),’,’,”) AS Gender
FROM @TAB
— Solution : 2 (if having even more than 2 distinct values)
–This will replace 1 value with 2, 2 with 3, 3 with n, n with 1
DECLARE @TAB TABLE (ID INT, Val VARCHAR(10))
INSERT INTO @TAB
SELECT 1, ‘AAA’
UNION ALL SELECT 2, ‘BBB’
UNION ALL SELECT 3, ‘BBB’
UNION ALL SELECT 4, ‘CCC’
UNION ALL SELECT 5, ‘CCC’
;WITH CTE AS(
SELECT ROW_NUMBER() OVER (ORDER BY Val) AS ROWID , Val
FROM ( SELECT DISTINCT Val FROM @TAB ) TAB
), CTEMIN AS (SELECT TOP 1 Val FROM CTE ORDER BY ROWID)
SELECT T1.ID, COALESCE(T2.Val, CTEMIN.Val) as Val
FROM (
SELECT ID, DENSE_RANK() OVER (ORDER BY Val) AS ROWID, Val FROM @TAB
) T1 LEFT JOIN CTE T2 ON T1.ROWID = T2.ROWID -1
CROSS APPLY CTEMIN
ORDER BY T1.ID
–Solution : 1
DECLARE @TAB TABLE (ID INT, Gender VARCHAR(10))
INSERT INTO @TAB
SELECT 1, ‘female’
UNION ALL SELECT 2, ‘male’
UNION ALL SELECT 3, ‘male’
DECLARE @list VARCHAR(100)
SET @list = (SELECT DISTINCT ‘,’ + Gender FROM @TAB FOR XML PATH(”))
SELECT ID, REPLACE(REPLACE(@list, ‘,’ + Gender, ”),’,’,”) AS Gender
FROM @TAB
— Solution : 2 (if having even more than 2 distinct values)
–This will replace 1 value with 2, 2 with 3, 3 with n, n with 1
DECLARE @TAB TABLE (ID INT, Val VARCHAR(10))
INSERT INTO @TAB
SELECT 1, ‘AAA’
UNION ALL SELECT 2, ‘BBB’
UNION ALL SELECT 3, ‘BBB’
UNION ALL SELECT 4, ‘CCC’
UNION ALL SELECT 5, ‘CCC’
;WITH CTE AS(
SELECT ROW_NUMBER() OVER (ORDER BY Val) AS ROWID , Val
FROM ( SELECT DISTINCT Val FROM @TAB ) TAB
), CTEMIN AS (SELECT TOP 1 Val FROM CTE ORDER BY ROWID)
SELECT T1.ID, COALESCE(T2.Val, CTEMIN.Val) as Val
FROM (
SELECT ID, DENSE_RANK() OVER (ORDER BY Val) AS ROWID, Val FROM @TAB
) T1 LEFT JOIN CTE T2 ON T1.ROWID = T2.ROWID -1
CROSS APPLY CTEMIN
ORDER BY T1.ID
DECLARE @TAB TABLE (ID INT, Gender VARCHAR(10))
INSERT INTO @TAB
SELECT 1, ‘female’
UNION ALL SELECT 2, ‘male’
UNION ALL SELECT 3, ‘male’
UPDATE T1
SET T1.Gender = T2.Gender
FROM (
SELECT DENSE_RANK() OVER (ORDER BY Gender) AS ROWID , ID, Gender
FROM @TAB
) T1 INNER JOIN (
SELECT Gender, ROW_NUMBER() OVER (ORDER BY Gender DESC) AS ROWID
FROM ( SELECT DISTINCT Gender FROM @TAB) T
) T2 ON T1.ROWID = T2.ROWID
SELECT * FROM @TAB
WITH Swap
AS (SELECT ‘male’ AS Gender
UNION ALL
SELECT ‘female’)
UPDATE T1
SET gender = Swap.Gender
FROM SimpleTable T1
CROSS JOIN Swap
WHERE Swap.Gender T1.Gender
WHERE Swap.Gender T1.Gender
Problem submitting code. The different then symbol is interpreted as empty HTML tag
UPDATE
SimpleTable
SET
Gender = SUBSTRING(‘fe’+ Gender, POWER(LEN(Gender) % 4, 2) + 1, 6)
Below also works well and can be implemented for more than one columns if to be swapped-
UPDATE GENDERS set gender=G2.SwappedGender
FROM GENDERS G1, (select GENDER, (CASE WHEN GENDER=’male’ THEN ‘FEMALE’ ELSE ‘MALE’ END) as SwappedGender from genders) as G2
WHERE G1.GENDER=G2.GENDER
I want to replace a Value from a record like as shown below,
AASDFADFAGE,CLOVER True,True
ISDRFOQUASA,GREG,FIRIEN True,True,True
ADADFDS,ZSERCL1VER True,True
DIOFFISFW,AFGREG,GGFRIEN True,True,True
Any suggestions please…
Sorry for the alignment,
It should be like
AASDFADFAGE,CLODFVER to True,True
ISDRFOQUASA,GREG,FIRIEN to True,True,True
ADADFDS,ZSERCL1VER to True,True
DIOFFISFW,AFGREG,GGFRIEN to True,True,True
CREATE FUNCTION fn_replacevalues(
@delimited NVARCHAR(MAX),
@delimiter NVARCHAR(100)
) RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @xml XML
DECLARE @retval VARCHAR(MAX)
SET @xml = N” + REPLACE(@delimited,@delimiter,”) + ”
SELECT @retval = STUFF((SELECT ‘,’ + ‘True’ FROM @xml.nodes(‘/t’) as records(r) FOR XML PATH(”)),1,1,”)
RETURN @retval
END
DECLARE @tab TABLE( val VARCHAR(MAX))
insert into @tab VALUES
(‘AASDFADFAGE,CLODFVER’), (‘ISDRFOQUASA,GREG,FIRIEN’),
(‘ADADFDS,ZSERCL1VER’), (‘DIOFFISFW,AFGREG,GGFRIEN’)
SELECT dbo.fn_replacevalues(val, ‘,’)
FROM @tab
Thanks Sandeep. i am using XML, without Function. There is a performance hit, So any other way.
DECLARE @tab TABLE( val VARCHAR(MAX))
insert into @tab VALUES
(‘AASDFADFAGE,CLODFVER’), (‘ISDRFOQUASA,GREG,FIRIEN’),
(‘ADADFDS,ZSERCL1VER’), (‘DIOFFISFW,AFGREG,GGFRIEN’)
select REPLICATE(‘True,’,LEN(val)-LEN(REPLACE(val,’,’,”)))+’True’
from @tab
Thanks sandeep, its working. I am looking for this kind of statement.
DECLARE @tab TABLE( val VARCHAR(MAX))
insert into @tab VALUES
(‘AASDFADFAGE,CLODFVER’), (‘ISDRFOQUASA,GREG,FIRIEN’),
(‘ADADFDS,ZSERCL1VER’), (‘DIOFFISFW,AFGREG,GGFRIEN’)
;WITH CTE (val, X)
AS
(SELECT val, CHARINDEX( ‘,’, val, 1)
FROM @tab
UNION ALL
SELECT val, CHARINDEX( ‘,’, val, X+1)
FROM CTE
WHERE CHARINDEX( ‘,’, val, X+1) 0)
SELECT val, REPLACE(RTRIM(REPLICATE (‘True ‘,COUNT(*) +1)),’ ‘, ‘,’) FROM CTE
GROUP BY val
DECLARE @tab TABLE ( val VARCHAR(MAX) )
INSERT INTO @tab
VALUES ( ‘asa’ ),
( ‘AASDFADFAGE,CLODFVER’ ),
( ‘ISDRFOQUASA,GREG,FIRIEN’ ),
( ‘ADADFDS,ZSERCL1VER’ ),
( ‘DIOFFISFW,AFGREG,GGFRIEN’ );
WITH Numbers ( X )
AS ( SELECT 1
UNION ALL
SELECT x + 1
FROM Numbers
WHERE X < 1000
)
SELECT val ,
REPLACE(RTRIM(REPLICATE('True ', SUM(N) + 1)), ' ', ',') AS Result
FROM Numbers T1
CROSS APPLY ( SELECT val ,
CASE WHEN SUBSTRING(val, x, 1) = ','
THEN 1
ELSE 0
END AS N
FROM @tab
WHERE x < LEN(val)
) T2
GROUP BY VAL
OPTION ( MAXRECURSION 1000 )
Thanks for the solution Eric,
USE tempdb
GO
DECLARE @SimpleTable TABLE (
ID INT, Gender VARCHAR(10)
)
INSERT INTO @SimpleTable (ID, Gender)
SELECT 1, ‘female’
UNION ALL
SELECT 2, ‘male’
UNION ALL
SELECT 3, ‘male’
Update S SET Gender = E.Gender from @SimpleTable S
CROSS JOIN (SELECT DISTINCT Gender from @SimpleTable)E
WHERE S.Gender E.Gender
select * from @SimpleTable
nice article…..:)
UPDATE SIMPLETABLE A
SET GENDER = (SELECT DISTINCT GENDER
FROM SIMPLETABLE B
WHERE A.GENDER B.GENDER)
Thanks
Yogesh. Thanks for reading.
what will be the answer for??
swapping gender column data without updating table.
There are many in comments.
being practical, some times gender column will be blank… may be data unavailable…. i prefer first approach because it is reliable… by the way great discussion thread
I don’t have a writable schema at hand, but this should do the trick.
We define a CTE with the replacement values, then join on that to find the replacement value for each value.
WITH
OriginalValues AS (
SELECT 1 AS ID, ‘female’ AS Gender
UNION ALL
SELECT 2, ‘male’
UNION ALL
SELECT 3, ‘male’
),
Replacements AS (
SELECT ‘male’ AS FromValue, ‘female’ AS ToValue
UNION ALL
SELECT ‘female’, ‘male’
)
SELECT
og.ID,
og.Gender AS OriginalValue,
rp.ToValue AS ReplacementValue
FROM OriginalValues og
JOIN Replacements rp ON (og.Gender = rp.FromValue)