SUBSTRING () and CONVERT () usage in SQL server

Announcement: QQ group: 124766907, if you are in. the NET field has unique insights and profound programming skills. It has expertise in a certain field. You are welcome to join this group. This group already has several MVPs, In the SL ,. NET, BS has accomplished people welcome to the group. If you have more than 4 years of experience, do not add it. This group is pursuing the high-end and top-level products. Thank you.

Today, we mainly use the SUBSTRING () and CONVERT () functions in SQL to add the number of the specified shipment date to the process billing number.

First, review the SUBSTRING () function:

SUBSTRING (1. String, 2. Start to intercept the position (including the current position), 3. Length of the string to be intercept)

----------------------------------------------------------------------------

-- Author: Life with heart
-- English name: wonder
-- Real name: Wan zegui
-- Objective: to collect, learn, organize, and review basic knowledge.

-- Time: 2010-03-02
-- CODE:
Declare @ string varchar (20)
Set @ string = '000000'
SELECT substring (@ string, 3, 2) -- truncates the second character after the third character
-- Result: 34
SELECT substring (@ string, 3, 5) --- truncate the fifth character after the third character
-- Rusult: 34567

 

Next, let's review the CONVERT () Conversion Function:

CONVERT (data_type [(length)], expression [, style])

CONVERT (1. SQL data type to be converted, 2. expression [, 3. Optional format,])

-- Author: Life with heart
-- English name: wonder
-- Real name: Wan zegui
-- Objective: to collect, learn, organize, and review basic knowledge.

-- Time: 2010-03-02
-- CODE:

Declare @ today datetime
Set @ today = getdate ()
Select convert (char (10), @ today, 120) -- converts a date to a 10-Bit String in '2017-01-01 'format

If you want to know what formats are available, please refer to MSDN or the following link:

Http://hi.baidu.com/27360411/blog/item/e632b3d4b87f9608a18bb7ca.html

Finally, I have reviewed the two functions in SQL. Now we can use this function to automatically append the date to the clothing serial number when printing the label or sticker, form a format.

SELECT 'ab10001underware '+ SUBSTRING (CONVERT (char (10), B. OrderDeliverDate, 120), 8, 3)

-- Result: AB10001UNDERWARE-02

-- This was what I met when I was in charge of printing the Logo sticker of my company. I reviewed it and recorded it.