Considerations behind an SQL server introductory question

I recently saw a small example of SQL Server, and found that it can be used as an introductory question for SQL server. The questions are as follows:

For example, there is a Contract Table Contract

Id	Name	Total	Buget
1	Contract name	100
2	Contract name 2	300	, 102,22,
3	Contract name 3	200	, 23,

Requirement: use an SQL statement to update the buget field of the table. If "," add "," is an English comma ). 10 points)

Create Table data:

View Code

 
 

  
  
Use Testdb2
  
  
Go
  
  
If not OBJECT_ID ('[Contract]') IS NULL
  
  
Drop table [Contract]
  
  
GO
  
  
Create table [Contract]
  
  
(ID int primary key identity (1, 1)
  
  
, [Name] nvarchar (50) null
  
  
, Total float null
  
  
, Buget Nvarchar (500) null)
  
  
Go
  
  
Insert into [Contract]
  
  
Select 'Contract name', 100, '102, 22'
  
  
Union all
  
  
Select 'Contract name 2', 300 ,'
  
  
Union all
  
  
Select 'Contract name 3', 300, '2014, 23 ,'
 
 

Analysis: This question is very simple at first glance. Because Replace is definitely used, it is natural to combine left and right to obtain the following statement.

Method 1:

 
 

  
  
update [Contract] set buget=','+buget where left(buget,1)=',' 
  
  
update [Contract] set buget=buget+',' where right(buget,1)=','  
 
 

If you can write an SQL statement, you can add 1 point.

 
 

  
  
update [Contract]   
  
  
set buget=(case when (left(buget,1)!=',' and right   (buget,1)!=',') then  ','+buget+','              
  
  
when left(buget,1)!=',' then ','+buget              
  
  
when right(buget,1)!=',' then  buget+','              
  
  
else buget        
  
  
end)  
 
 

If you can start from the string's switch and end, and combine the Reverse, you can refer to the following method:

Method 2:

 
 

  
  
update [Contract] set buget=','+buget where charindex(',',buget)<>1  
  
  
update [Contract] set buget=buget+',' where charindex(',',reverse(buget))<>1  
 
 

This method mainly involves charindex and reverse functions.

To be honest, I thought these two ideas at the time, which are also common basic usage in SQL. However, the third method appears unexpectedly.

Method 3:

 
 

  
  
UPDATE [contract] SET Buget = ','+Buget+',' 
  
  
UPDATE [contract] SET Buget = REPLACE(Buget,',,',',')  
 
 

Analysis: The main highlight of this method is not the subtlety of the syntax, but the difference in its thinking from the conventional one. Add a comma to both sides, and then replace the double comma with a single comma. This is indeed commendable in actual programming. In other words, If you haven't thought about it in advance, it reflects the agile response of the person who understands the question and the open thinking.Therefore, you can add at least three more points.

Of course, this statement still has bugs. For example, if there are two commas in the original bug field, the content that should not be updated will be updated during Replace. However, with a slight correction, you can limit the scope of replace,

Inspired by this idea, we can use the following methods:

Method 4:

 
 

  
  
UPDATE [contract] SET Buget = substring(BuGet,2,len(BuGet)-1) wherecharindex(',',buget)=1  
  
  
UPDATE [contract] SET Buget = substring(BuGet,1,len(BuGet)-1) wherecharindex(',',reverse(buget))=1  
  
  
UPDATE [contract] SET BuGet = ','+BuGet+','  
 
 

This method removes the commas (,) on both sides and adds a comma (,) to each record. Compared with method 3, this method is a little cumbersome, and it also reflects the clever use of method 3.

Of course, you can also make some corrections based on the previous ideas. I will not repeat them here. Please think for yourself.

Perception: Sakyamuni said, "life requires six practices: Provisioning, giving up the ring, enduring humiliation, refined development, meditation, and wisdom .",This is also true for SQL programming, or a field of C #, Java, or even Javascrip. Technology is dead, and ideas are fresh,Sometimes, the idea can easily break through the dead corners that are hard to achieve by technology..To a certain extent, we will find that the subconscious has been filled with inertial thinking and cannot accept new ways of thinking or ideas. If this persists for a period of time, we should be alert, leave your mind blank, place yourself as a beginner, and start the "refined" Cultivation again!

Edit recommendations]