SQL SERVER : Concat String

Post by Goborijung at 2021-07-29 10:07:35 | ID: 1296

Select CONCAT(f.Customer,' - ',cus.Name) as Customer 
From FBPurchaseReceipt f left join Customer cus on cus.OID = f.Customer Where TransactionNo = 'ref017-26023'

SQL SERVER : Convert Decimal tox Hex

Post by Goborijung at 2021-07-05 13:54:54 | ID: 1273

SELECT FORMAT(9,'X')
SELECT FORMAT(10,'X')
SELECT FORMAT(11,'X')
SELECT FORMAT(12,'X')

SQL SERVER : CREATE TABLE (ตัวอย่างการสร้าง Table ทีมี DataType แบบต่างๆ)

Post by Goborijung at 2022-03-26 11:47:40 | ID: 1426

CREATE TABLE books (
  id              INT           NOT NULL    IDENTITY    PRIMARY KEY,
  title           VARCHAR(100)  NOT NULL,
  primary_author  VARCHAR(100),
)

Create Table GOODS_NAME
(
	Goods_id char(10) default '' not null Primary key,
	Good_name char(30) default '' not null,
	cost_unit decimal(8,2) default 0 not null
)

Create Table CUS_NAME
(
	Cus_id char(5) default '' not null Primary key,
	Cus_name char(30) default '' not null
)

Create Table H_ORDER
(
	Order_no int default 0 not null Primary key,
	Cus_id char(5) default '' not null,
	Order_Date datetime default '2000-01-01' not null,
	Foreign key (Cus_id) References CUS_NAME(Cus_id)
)

Create Table  D_ORDER
(
	Order_no  int  default 0 not null,
	Goods_id  char(10)  default '' not null,
	Ord_date  datetime  default '2000-01-01' not null,
	Fin_date datetime default '2000-01-01' not null,
	Amount  decimal(10,2)  default 0 not null,
	COST_UNIT decimal(8,2) default 0 not null,
	TOT_PRC decimal(10,2) default 0 not null,
	 Primary key (Order_no,Goods_id),
	 Foreign key  (Order_no) References H_ORDER(Order_no),
	Foreign key (Goods_id) References GOODS_NAME(Goods_id)
)

Create Table M_ORDER
(
	Cus_id char(5) default '' not null,
	Goods_id char(10) default '' not null,
	Doc_date datetime default '2000-01-01' not null,
	Ord_date datetime default '2000-01-01' not null,
	Fin_date datetime default '2000-01-01' not null,
	Sys_date datetime default '2000-01-01' not null,
	Amount decimal(10,2) default 0 not null,
	cost_tot decimal(10,2) default 0 not null,
	Primary key(Cus_id,Goods_id,Doc_date,Ord_date,Fin_date)
)

Select * From GOODS_NAME
Select * From CUS_NAME
Select * From H_ORDER
Select * From D_ORDER
Select * From M_ORDER

SQL SERVER : Setup Tabs

Post by Goborijung at 2021-05-21 08:37:05 | ID: 1213

// How to Setup Tab
1. ไปที่ Menu Tools > Text Editor > XML > Tabs

SQL SERVER : Add ODBC Driver

Post by Goborijung at 2023-03-23 14:30:32 | ID: 1870

Controlpanel > เลือก View by: Small Icons > Windows Tools > ODBC Data Sources

SQL SERVER : ALL Function in MSSQL

Post by Goborijung at 2020-07-01 09:50:31 | ID: 644

https://www.w3schools.com/sql/sql_ref_sqlserver.asp

SQL SERVER : ASCII (Conver Number to Ascii) , CHAR (Conver Ascii to Number)

Post by Goborijung at 2023-08-16 11:18:52 | ID: 1962

Select 
ASCII(6)-5 as Number_to_Ascii --54 to 49 | 6-5 = 1 , 1 ใน Ascii คือ 49
,CHAR(49) as Ascii_to_Char_Number --1 | รหัส Ascii 49 คือเลข 1
,CHAR(ASCII(SUBSTRING(PASSWORD,1,1))-5) + CHAR(ASCII(SUBSTRING(PASSWORD,2,1))-5) + CHAR(ASCII(SUBSTRING(PASSWORD,3,1))-5) + CHAR(ASCII(SUBSTRING(PASSWORD,4,1))-5) as pwDecode
,'|' as [|],* From PDS_USER Where 1=1 And EMPID = '6501112'

SQL SERVER : Basic SQL

Post by Goborijung at 2020-06-26 12:41:24 | ID: 638

http://www.widebase.net/database/sql/sqlquery/sqlquery.shtml

SQL SERVER : Bypass Single Quote

Post by Goborijung at 2020-05-29 10:45:41 | ID: 591

Example

INSERT INTO my_table VALUES ('hi, my name''s tim')

Output : hi, my name's tim

SQL SERVER : Convert Datetime

Post by Goborijung at 2021-05-15 11:55:01 | ID: 1193

https://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/

Ex
-- Default Date : 1990-08-02
Select Convert(nvarchar,cast('1990-08-02' as date),105) as cDate // Out : 02-08-1990