SQL string Operations and other

1. String manipulation functions

Suppose @a= ' abc789 '

1.charindex returns the starting position of a character or string in another string

CHARINDEX (' B ', @a)--Results: 2

2.substring returns part of a character, binary, text, or image expression (intercepts a string)

SUBSTRING (@a,1,len ($a))--substring (@a,start,length)

Results: abc789

3.left returns the specified number of characters starting from the left of the string

Left (@a,1)--Result: a

Left (@a,3)--Results: ABC

4.right returns the specified number of characters starting from the right of the string

Right (@a,1)--Results: 9

Right (@a,3)--Results: 789

right(‘000000‘+cast(id asvarchar),7)  --左侧六位自动补零

5. Patindex returns the starting position of the first occurrence of a pattern in a specified expression

Patindex ('%[^0-9]% ', @a)--Results: 1

Patindex ('%[0-9]% ', @a)--Results: 4

6.stuff deletes a specified length of character and inserts another set of characters at the specified starting point

Stuff (' abc789 ', 2, 3, ' ijklmn ')--stuff (@a,start,length,replacestr)

Results: aijklmn89

Example:

SELECT ' Cherry ', 1 unionall
SELECT ' Cocoa (milk) '

Select Case charindex (' (', [name]) when 0 then [name] else substring ([Name],0,charindex (' (' (') [name]) ' End from Table_1

Query Result:

...

Cherry

Cocoa--it's been intercepted.

7.replace Replace carriage return, line feed, carriage return line feed

SELECT *, REPLACE (detail, CHAR (+), ' <br> ')

SELECT *, REPLACE (detail, CHAR (Ten), ' <br> ')

SELECT *, REPLACE (detail, char) + char (Ten), ' <br> ')

2. Whether the table variable is empty

[Email Protected]=count (1) from @resultTempIF (@tmpCount >0) BEGIN ...        END

3.sp_executesql execute dynamic SQL, support parameters

DECLARE @strsql NVARCHAR SET @strsql = ' SELECT @total =sum (a.total) from ' [email protected]+ '. Dbo.table_1 A, TestDBA.dbo.Project b WHERE a.code=b.code ' EXEC sp_executesql @strsql, N ' @total as FLOAT output ', @alltotal output

4. Take the maximum value in multiple fields

Select Max (M) from (    select Max (AA) m from TableName    UNION ALL    select Max (BB) from TableName    UNION ALL    select Max (cc) from TableName) t

5. Date function

1. Current system date, time

Select GETDATE ()

2.dateadd returns a new datetime value based on adding a period of time to a specified date

Example: Add 2 days to the date

Select DATEADD (day,2, ' 2004-10-15 ')--return: 2004-10-17 00:00:00.000

3.datediff returns the number of date and time boundaries across two specified dates.

Select DateDiff (Day, ' 2004-09-01 ', ' 2004-09-18 ')--return: 17

4.datepart returns an integer representing the specified date portion of the specified date.

Select DATEPART (month, ' 2004-10-15 ')--return 10

5.datename returns a string representing the specified date part of a specified date

Select Datename (Weekday, ' 2004-10-15 ')--return: Friday

6. Day (), month (), year ()--can be compared with datepart

7. Current date, current time

Select Current date =convert(varchar), GETDATE (), 120), Current time =convert(varchar (8), GETDATE (), 114)

8. The first few weeks of the year, today's week

Select today is the week of =datename (DW, ' 2004-10-15 ')

Select this year how many weeks =datename (week, ' 2004-10-15 '), today is the week of =datename (weekday, ' 2004-10-15 ')

9. Short Date format

Select CONVERT (varchar), getDate (), 120)

Select CONVERT (VARCHAR), GETDATE (), 23)

      functions                       & nbsp                       &NBSP parameters/Features
  Getdat E ()                                 &NBSP ;         Return to the current date and time of the system
  DateDiff (interval,date1,date2)           &NBS P   Returns the difference between Date2 and date1 two dates in interval specified date2-date1
  DATEADD (interval,number,date)             interval specified, plus the date after number
  DatePart (interval,date)         & nbsp               Return date interval the integer value corresponding to the specified part
  datename (interval,date)                       Return date interval the string name corresponding to the specified section

the setting values for the parameter interval are as follows:

Value abbreviations (SQL Server) Access and ASP descriptions
Years: Year Yy yyyy 1753 ~ 9999
Season: Quarter Qq Q 1 ~ 4
Months: Month Mm M 1 ~ 12
The number of days of the year, the Day of the Year: Days of the yearly Dy y 1-366
Days: Day Dd D 1-31
Day of the week, Day of the week: Weekday Dw w 1-7
Week, week of the year: Week Wk ww 0 ~ 51
When: Hour Hh h 0 ~ 23
Score: Minute Mi N 0 ~ 59
Seconds: Second Ss s 0 ~ 59
MS: Millisecond Ms-0 ~ 999

In Access and ASP, date () and now () are used to obtain system datetime, where Datediff,dateadd,datepart is also used in Access and ASP, and the usage of these functions is similar

One month first day
SELECT DATEADD (mm, DATEDIFF (Mm,0,getdate ()), 0)

Monday of the Week

SELECT DATEADD (wk, DATEDIFF (Wk,0,getdate ()), 0)

The first day of the year

The first day of the year is now displayed with a time interval of year (yy).

SELECT DATEADD (yy, DATEDIFF (Yy,0,getdate ()), 0)

first day of the quarter

If you want to calculate the first day of the quarter, this example tells you how to do it.

SELECT DATEADD (QQ, DATEDIFF (Qq,0,getdate ()), 0)

the night of the day

SELECT DATEADD (DD, DATEDIFF (Dd,0,getdate ()), 0)

The last day of last month

It is obtained by subtracting 3 milliseconds from this example on the last day of one months. One thing to keep in mind is that the time in SQL Server is exactly 3 milliseconds. That's why I need to subtract 3 milliseconds to get the date and time I want.

SELECT DateAdd (Ms,-3,dateadd (mm, DATEDIFF (Mm,0,getdate ()), 0))

The time portion of the calculated date contains the time at which SQL Server can record the last moment of the day ("23:59:59:997").

last day of last year

SELECT DateAdd (Ms,-3,dateadd (yy, DATEDIFF (Yy,0,getdate ()), 0))

last day of the month

SELECT DateAdd (Ms,-3,dateadd (mm, DATEDIFF (M,0,getdate ()) +1, 0))

The last day of the year

SELECT DateAdd (Ms,-3,dateadd (yy, DATEDIFF (Yy,0,getdate ()) +1, 0))

The first Monday of the month

Select DATEADD (wk, DATEDIFF (Wk,0,dateadd (Dd,6-datepart (Day,getdate ()), GETDATE ()), 0)

6. Amount plus comma

DECLARE @i Money
SET @i=123456789.00
SELECT CONVERT (varchar,@i,1)

DECLARE @i VARCHAR (50)
SET @i=123456789.00
SELECT CONVERT (Varchar,convert (money,@i), 1)

7. Check the library name, library ID

SELECT Name from Master. sysdatabases ORDER by Name

8. Determine if there are table names, stored procedures, functions

--Determine if the library exists
if exists (select * from Master.. sysdatabases where name=n ' library name ')
Drop DATABASE Database name
GO

--Determine if the name of the table to be created exists
if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ Table name] ') and OBJECTPROPERTY (ID, N ' isusertable ') = 1)
drop table [dbo]. [Table name]
GO

--whether the column exists
IF col_length (' table name ', ' column name ') is NULL
ALTER TABLE name DROP constraint default value name
Go
ALTER TABLE table name drop column name
Go

--Determine if you want to create a temporary table exists
If object_id (' tempdb.dbo. #Test ') is not Null
drop table #Test
GO

--Determine if the stored procedure name to be created exists
if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ Stored procedure name] ') and OBJECTPROPERTY (ID, N ' isprocedure ') = 1)
drop procedure [dbo]. [Stored procedure name]
GO

--Determine if the name of the view you want to create exists
if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ View name] ') and OBJECTPROPERTY (ID, N ' isview ') = 1)
Drop view [dbo]. [View name]
GO

--Determine if the name of the function to be created exists
if exists (select * from sysobjects where xtype= ' fn ' and name= ' function name ')
if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ Function name] ') and xtype in (n ' FN ', n ' IF ', n ' TF '))
Drop function [dbo]. [function name]
GO

9.SQL2008 Configuration Management Tool Service shows remote procedure call failed

Open the programs and features, uninstall a call "Microsoft SQL Server 2012LocalDB", reopen SQL Configuration Manager, and find out.

10. When you restore the database, you are prompted to "gain exclusive access to the database because the database is in use"

1, set the database to work in single-user mode.
Setup method: Right-click on the database you want to restore, select Properties-> Options-> Status-> Restrict Access-> "single" On the right-click menu command. This is the menu command for SQLSERVER2005, other versions please find it yourself.

2, the use of SQL statements to kill all the processes that are using the database, I used to do a SQL Server operation of the small tool has written this function of SQL, posted for your reference:
Copy the code code as follows:
DECLARE @dbname varchar (50)
Set @dbname = ' database name '
DECLARE @sql varchar (50)
Declare cs_result cursor local for select ' Kill ' +cast (spid as varchar) from sys.sysprocesses where Db_name (dbid) [Emai L protected]
Open Cs_result
FETCH NEXT from Cs_result to @sql
While @ @fetch_status =0
Begin
Execute (@sql)
FETCH NEXT from Cs_result to @sql
End
Close Cs_result
Deallocate Cs_result
The SQL statement uses the cursor to loop through all the processes that are using the database and kills the process with the kill command.

3. Using SQL statements, disconnect all user links and roll back all transactions , with the following SQL statements:
Copy the code code as follows:
ALTER database [DB name]
SET OFFLINE with ROLLBACK IMMEDIATE

Note: When using method 2 and 3 o'clock, do not execute under the desired restored database, it is recommended to execute under the master database.

11. Do not allow changes to be saved, prevent changes that require re-creation of tables

In SQL Server Management Studio, click the menu "Tools"-"Options",
Open the Options dialog box, expand Designers, and cancel the Prevent saving changes that require the table to be recreated check box

SQL string operations and other (GO)