SQL Server splits a field into multiple fields based on a character display

The data information for a table (person) in a SQL Server database is:

Id	Address
1	Pingshan Garden-4 units-12 blocks-203
2	Xiangshan Garden-3 units-22 blocks-304

Now there is the need to change the address information display form to 4 columns, that is, cell name, unit number, building number, room number divided into 4 columns for display

Id	Community Name	Unit number	Building No.	Room number
1	Pingshan Garden	Unit 4	Building 12	203
2	Xiangshan Garden	Unit 3	Building 22	304

Two scenarios are described:

The first: The simplest approach is to call the SQL Server with its own function parsename to split it, but note that you can only split it into 4 fields at a maximum.

The implementation for this example: ParseName By default is based on '. ' is split, so the first thing to do is to replace the '-' in the field with '. '

SELECT Address, ParseName (REPLACE ([address], '-', '. '), 4) as cell name,--if the contents of the field are 4 units-12 blocks-203 Then the information in the Cell Name field is Nullparsename ( replace ([address], '-', '. '), 3) as unit number, parsename (replace ([address], '-', '. '), 2) as building number, parsename (replace ([address], '- ', '. '), 1) as room number from person

ParseName Detailed:

ParseName ('object_name

' object_name'

The name of the object to retrieve the specified part of. The object_name data type is sysname. This parameter is an optional qualified object name. If all parts of the object name are qualified, this name can contain four parts: the server name, the database name, the owner name, and the object name.

Object_piece

The part of the object to return. The data type of the object_piece is an int value and can be the following values:

1 = object Name

2 = schema Name

3 = database name

4 = Server name

The ParseName function does not indicate whether an object of the specified name exists. ParseName returns only the specified part of the specified object name.

The second scenario: thinking: Write a function that uses substring and charindex to intercept and split a field based on a specific split symbol,

-This function returns a table ALTER function [dbo]. [F_splitlianxi] (@str NVARCHAR,--The string to be split @fengefu NVARCHAR (20)--the symbol to be split) RETURNS @table table (ID int,val NVARCHAR ()) asbegin DECLARE @index int, @startsplit int, @id int [email protected] The subscript at the location where the delimiter is located @startsplit SELECT @index =charindex (@fengefu, @str), @startsplit =1, @id =1 while @index >0 is at the beginning of each split GIN IF @id >1-The code in this method body is executed after the first loop start SELECT @[email protected]+len (@fengefu)-Split The starting position equals the previous character subscript position + the length of the character itself SELECT @index =charindex (@fengefu, @str, @startsplit) END IF @i Ndex>0--the first cycle intercept position begins with @startsplit=1 INSERT into @table VALUES (@id, SUBSTRING (@str, @startsplit, @[email protected])--The length of the start of the string to intercept end ELSE begin--The last loop at this time due to the horse Does not match the split character @index = 0 intercepts the remaining string INSERT into @table VALUES (@id, SUBSTRING (@str, @startsplit, LEN (@str) [Email protec ted]+1)) END SELECT@[email protected]+1 End RETURN End 

Invoke rule:

Select [Address], (select Val from [F_splitlianxi] ([Address], '-') WHERE id=1) as cell name, (select Val from [F_splitlianxi] ([Ad Dress], '-') where id=2) as the unit number, (select Val from [F_splitlianxi] ([Address], '-') where id=3) as building number (select Val from [f_spli Tlianxi] ([Address], '-') WHERE id=4) as room number from person

PS: There is also a more brutal way of writing is to append a new column by intercepting the string without creating a function direct Select

For example, there is a requirement that there is a field in the T_person table birth the information in the form does not conform to the rules when it is entered.

Id	Birth
1	1900/2/12
2	1898/2/3
3
4	Null

A unified form is now required: the form of 1987-05-03:

Here is the process:

UPDATE dbo. T_person SET birth=substring (Birth,1,charindex ('/', Birth)-1)--Get year + '-' +case when SUBSTRING (Birth,charindex ('/'), Birth) +1,charindex ('/', Birth,charindex ('/', Birth) +1)-(CHARINDEX ('/', Birth) +1)) <10--intercept the month and 10 to compare then ' 0 ' + SUBSTRING (Birth,charindex ('/', Birth) +1,charindex ('/', Birth,charindex ('/', Birth) +1)-(CHARINDEX ('/', Birth) +1))-- When less than 10, append 0 before the month and return to else SUBSTRING (Birth,charindex ('/', Birth) +1,charindex ('/', Birth,charindex ('/', Birth) +1)-( CHARINDEX ('/', Birth) +1)--more than 10 when the intercept month returns end--gets the month + '-' +case when SUBSTRING (Birth,charindex ('/', Birth,charindex ('/') , Birth) +1) +1,len (Birth)-charindex ('/', Birth,charindex ('/', Birth) +1)) <10--intercept day and 10 compare then ' 0 ' +substring (Birth, CHARINDEX ('/', Birth,charindex ('/', Birth) +1) +1,len (Birth)-charindex ('/', Birth,charindex ('/', Birth) +1))--when less than 10 Append a 0 to the day before returning elsesubstring (Birth,charindex ('/', Birth,charindex ('/', Birth) +1) +1,len (Birth)-charindex ('/', Birth, CHARINDEX ('/', Birth) +1)--more than 10 when the Intercept day returns to end--to get the day where CHARINDEX ('/', Birth) >0--Prevents the absence/information field from being intercepted by a stringError no '/' returns 0 but null gets the value is null--comment Select CHARINDEX ('/', ' 123 ')--the result is 0SELECT CHARINDEX ('/', NULL)--the result is null 

The result after execution is:

Id	Birth
1	1900-02-12
2	1898-02-03
3
4	Null

SQL Server splits a field into multiple fields based on a character display