SQL Server：在SELECT语句中拆分字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23341039/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server : split string in SELECT statement
提问by user3260664
I want to split date in column in 3 fields, I use this query
我想在 3 个字段的列中拆分日期，我使用此查询
SELECT
SUBSTRING(Account.date, 1, 2) AS "Month",
SUBSTRING(Account.date, 4, 2) AS "Day",
SUBSTRING(Account.date, 7, 4) AS "Year"
FROM Account
Almost all data is in format 02/11/2000, but some of it can be 02/November/2000or 2/11/2000.
几乎所有数据都是格式化的02/11/2000，但其中一些可以是02/November/2000或2/11/2000。
Only common thing is that data separated by /. How can I separate this column using the delimiter?
唯一的共同点是数据以/. 如何使用分隔符分隔此列？
回答by Szymon
You can do it this way by using CHARINDEXand SUBSTRINGfunctions
你可以通过使用CHARINDEX和SUBSTRING函数来做到这一点
select
LEFT(Account.date, CHARINDEX('/', Account.date) - 1),
SUBSTRING(Account.date, CHARINDEX('/', Account.date) + 1, LEN(Account.date) - CHARINDEX('/', Account.date) - CHARINDEX('/', Account.date, CHARINDEX('/', Account.date)) - 2),
REVERSE(LEFT(REVERSE(Account.date), CHARINDEX('/', REVERSE(Account.date)) - 1))
FROM Account
回答by Mudassir Hasan
Surprisingly CAST('2/November/2000' as datetime)works (checked on SQL Server 2008), gives value 2000-11-02 00:00:00.000
令人惊讶的CAST('2/November/2000' as datetime)工作（在 SQL Server 2008 上检查），给出值2000-11-02 00:00:00.000
SELECT
Month(CAST(Account.date AS DateTime)) "Month",
Day(CAST(Account.date AS DateTime)) "Day",
Year(CAST(Account.date AS DateTime)) "Year",
FROM Account
But as rightly pointed out in comment how do you know if "02/11/2000" is November 2, 2000 or February 11, 2000?
但是正如评论中正确指出的那样，您如何知道“02/11/2000”是 2000 年 11 月 2 日还是 2000 年 2 月 11 日？
Also the spelling of Month names must be absolutely correct else conversion fails. Since you are storing dates as string there is chance that entry like November , Agust etc could have been made .
此外，月份名称的拼写必须绝对正确，否则转换失败。由于您将日期存储为字符串，因此可能已经创建了诸如 11 月、8 月等条目。
You should never store date values as strings.
您永远不应该将日期值存储为字符串。
回答by GarethD
You can abuse the PARSENAMEfunction slightly here:
您可以在这里稍微滥用PARSENAME函数：
SELECT FirstPart = PARSENAME(REPLACE(Account.Date, '/', '.'), 3),
SecondPart = PARSENAME(REPLACE(Account.Date, '/', '.'), 2),
ThirdPart = PARSENAME(REPLACE(Account.Date, '/', '.'), 1)
FROM (VALUES
('02/November/2000'),
('2/11/2000')
) Account (Date);
Will give:
会给：
FirstPart SecondPart ThirdPart
02 November 2000
2 11 2000
I would however, highly recommend storing your dates using the appropriate data type!. SQL Server 2012 has the TRY_CONVERTfunction which can make such conversions easier, but you still need to know what format your string date is in, 2/11/2000could be the 2nd November, or 11th February depending on your regional settings.
但是，我强烈建议使用适当的数据类型存储您的日期！. SQL Server 2012 具有TRY_CONVERT函数可以使此类转换更容易，但您仍然需要知道字符串日期的格式，2/11/2000可能是 11 月 2 日或 2 月 11 日，具体取决于您的区域设置。
回答by Balaji
Try this:
尝试这个：
DECLARE @VALUE VARCHAR(100)<BR>
SET @VALUE ='2/11/2000' <BR>
SELECT SUBSTRING(@VALUE,0,CHARINDEX('/',@VALUE,0)),
SUBSTRING(@VALUE,
CHARINDEX('/',@VALUE,0)+1,
(CHARINDEX('/',@VALUE,(CHARINDEX('/',@VALUE,0)+1)) -
CHARINDEX('/',@VALUE,0) - 1)
),RIGHT(@VALUE,4)
回答by Nickolay Komar
You can use the combination of CharIndex and Substring function in SQL to split the string based on delimiter.
您可以使用 SQL 中的 CharIndex 和 Substring 函数的组合来根据分隔符拆分字符串。
You can check CharIndex examples here SQL Server 2005 Using CHARINDEX() To split a stringand here SQL Server - find nth occurrence in a string
您可以在此处检查 CharIndex 示例SQL Server 2005 Using CHARINDEX() To split a stringand here SQL Server - find nthoccurrence in a string
回答by wumpz
Assuming your database column account.datecontains a valid datetimeor datevalue you should using SQLServers date functions like:
假设您的数据库列account.date包含一个有效值datetime或date值，您应该使用 SQLServers 日期函数，例如：
select month(getDate()) as "Month",
day(getDate()) as "Day",
year(getDate()) as "Year"
I replaced your column account.dateby a getDate()to have some test values. This maps to your SQL in the following way
我用account.datea替换了您的列getDate()以获得一些测试值。这以下列方式映射到您的 SQL
SELECT
month(Account.date) AS "Month",
day(Account.date) AS "Day",
year(Account.date) AS "Year"
FROM Account
Storing these date values as varchars would be IMHO a design flaw of your database structure. Dates are formatted in multiple ways to text. This is the presentation of your database data. To process your data you would always preprocess your text dates. That is bad practice.
将这些日期值存储为 varchars 恕我直言是您数据库结构的设计缺陷。日期以多种方式格式化为文本。这是您的数据库数据的展示。要处理您的数据，您总是会预处理您的文本日期。那是不好的做法。
If you have indeed varchar values there are several SO questions like: How do I split a string so I can access item x?.
如果您确实有 varchar 值，则有几个问题，例如：如何拆分字符串以便访问项目 x？.
