如何在SQL中以下列格式获取字符串 [英] How to get string in following format in SQL
本文介绍了如何在SQL中以下列格式获取字符串的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有以下格式的字符串:晚上8点 - 晚上10点
现在我想在sql中以8-10 PM格式输入字符串
我尝试了什么:
选择(SUBSTRING(' 8.00p.m', 0 ,CHARINDEX(' 。',' 8.00p.m', 0 )) )+ ' - +(SUBSTRING('' 10.00p.m', 0 ,CHARINDEX( ' 。',' 10.00p。 m', - 2)))
解决方案
说实话,你如果您需要这样做,可能会将其存储错误。
将数据存储为NVARCHAR非常简单,并且您的应用程序代码也很简单,但是当您想要使用它时,它是一个可以使用的PITA ,因为你是要看的字符串。
相反,我将它存储为两个非字符串列,一个开始和一个结束时间列。列内容取决于我想对数据做什么,以及它的用途,但最简单的解决方案是将其存储为整数:午夜时分。然后你为你的字符串格式化变得非常简单:我写了一个接受整数的SQL函数,并返回时间字符串:
CREATE FUNCTION [dbo]。[HourToString]
(
- 在此处添加函数的参数
@ Hour INT
)
RETURNS NVARCHAR ( 4 )
AS
BEGIN
RETURN CASE WHEN @ Hour = 0 那么 ' 12AM'
WHEN @ Hour < 12 那么 CONVERT ( NVARCHAR ( 4 ), @ Hour )+ ' AM'
WHEN @ Hour = 12 那么 ' 12PM'
ELSE CONVERT ( NVARCHAR ( 4 ), @ Hour - 12 )+ ' PM'
END
END还有其他一切ts以后也更容易使用。比如检查你是否在那段时间,等等!
请阅读 MSDN文档 [ ^ ]。在那里你可以找到你想要的一切。
DECLARE < span class =code-sdkkeyword> @ myDate DATETIME = GETDATE()
SELECT LEFT ( CONVERT ( VARCHAR ( 20 ), @ myDate , 114 ), 5 ) AS MyCustomTimeFormat
- 返回:
- 9时47 跨度>
I have string in following format : 8.00p.m - 10.00p.m
now i want string in 8-10 PM format in sql
What I have tried:
select (SUBSTRING('8.00p.m',0,CHARINDEX('.','8.00p.m',0)))+ '-' +(SUBSTRING('10.00p.m',0,CHARINDEX('.','10.00p.m',-2)))
解决方案
To be honest, you're probably storing it wrong if you need to do that.
Storing data as NVARCHAR is simple, and easy for your app code, but it's a PITA to work with when you want to do anything with it, as you are string to see.
Instead, I'd store it as two non-string columns, a "start" and an "end" time column. The column content would depend on what I wanted to do with the data, and what it's for, but the simplest solution is to store it as an integer: "hours since midnight". Then your formatting it for your string becomes pretty simple: I'd write an SQL function that accepts an integer, and returns the time string:
CREATE FUNCTION [dbo].[HourToString] ( -- Add the parameters for the function here @Hour INT ) RETURNS NVARCHAR(4) AS BEGIN RETURN CASE WHEN @Hour = 0 THEN '12AM' WHEN @Hour < 12 THEN CONVERT(NVARCHAR(4), @Hour) + 'AM' WHEN @Hour = 12 THEN '12PM' ELSE CONVERT(NVARCHAR(4), @Hour - 12) + 'PM' END ENDAnd everything else gets easier to work with later as well. Such as checking if you are in that time period, and so on!
Please, read MSDN documentation[^]. There you'll find everything what you want.
DECLARE @myDate DATETIME = GETDATE() SELECT LEFT(CONVERT(VARCHAR(20), @myDate, 114), 5) AS MyCustomTimeFormat --returns: --09:47
这篇关于如何在SQL中以下列格式获取字符串的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文