dwanue IT Life

SQL 常用字串處理語法說明：包含 substring、left、right、upper、lower、ltrim、rtrim、、、

• 取字串中部分字元
  • 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."

原因是伺服器上沒有ACCESS 引擎

--檢查是否為SQL SERVER合法日期格式

SELECT ISDATE('2004-03-01')

select * from [dbo].[onlinemsg_main_view]