MS SQL Server

Reset identity seed after deleting records in SQL Server

คือลบ record แล้ว และอยากให้ reset ค่า id ให้ใช้คำสั่ง

DBCC CHECKIDENT ('[TestTable]', RESEED, 0);
GO

To view the compatibility level of a database

USE AdventureWorks2012;  
GO  
SELECT compatibility_level  
FROM sys.databases WHERE name = 'AdventureWorks2012';  
GO

Transforms each list of tags and joins them with the original row:

SELECT ProductId, Name, value  
FROM Product  
    CROSS APPLY STRING_SPLIT(Tags, ',');  

SQL Server CAST() Function

SELECT CAST(25.65 AS int);

SQL Server ISNUMERIC() Function

SELECT ISNUMERIC('4567');  -- return 1
SELECT ISNUMERIC('45a67'); -- return 0
SELECT column1 
FROM table 
WHERE ISNUMERIC(column1) = 1

SQL Server REPLACE() Function

SELECT REPLACE('SQL Tutorial', 'T', 'M');

Remove Non-Numeric Characters

stuff(Col, 1, patindex('%[0-9]%', Col)-1, '')
CAST(stuff(Col, 1, patindex('%[0-9]%', Col)-1, '') AS bigint) AS cleaned_numeric