SQL 常用字串處理語法

取字串中部分字元

SELECT Mem_ID, substring(Mem_ID,4,6) AS MEM_ID_6 

FROM members

語法：substring(欄位, 起始字元, 取幾字元)

ex: A123456789  => substring(Id,4,6) => 456789

取左側字元

SELECT LEFT(engname,5) 

FROM members

語法：left(欄位, 位數)

ex: SELECT LEFT('abcdefghijk',5) => abcde

取右側字元

SELECT RIGHT(engname,5) 

FROM members

語法：right(欄位, 位數)

ex: SELECT RIGHT('abcdefghijk',5) => ghijk

字串大小寫轉換 upper、lower

SELECT upper(engname) 

FROM members

語法：upper(欄位）

ex: michael => upper => MICHAEL

SELECT lower(engname) 

FROM members

語法：lower(欄位）

ex: MICHAEL => lower => michael

去除無謂空白 ltrim、rtrim

SELECT LTRIM(email) 

FROM members

語法：ltrim(欄位)

ex: "    abd@com" => ltrim => "abd@com"

SELECT RTRIM(memo) 

FROM members

語法：rtrim(欄位)

ex:"Have a nice day.     " => rtrim =>"Have a nice day."

[SQL] 列出DB裡面所有的 TABLE 與 Column

列出DB裡面所有的 TABLE 與 Column


'List All DB Table name
SELECT * 
FROM INFORMATION_SCHEMA.TABLES
where table_type='base Table'
order by TABLE_NAME




'列出Table_Name,Column順序,Column欄位型態,Column,Column長度,ColumnIs_Identity 

select 
    tab.name as Table_Name, 
    col.Column_id,
    col.name as Column_Name, 
    t.name as Data_type,    
    col.Max_Length,
COL.Is_Identity 
from sys.tables as tab
    inner join sys.columns as col
        on tab.object_id = col.object_id
    left join sys.types as t
    on col.user_type_id = t.user_type_id
order by 
    table_name,   col.column_id