CODEWITHSUNDEEP
sqlsql-serversubstring
user3260664
user3260664

Reputation: 155

SQL Server : split string in SELECT statement

I want to split date in column in 3 fields, I use this query

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/2000 or 2/11/2000.

Only common thing is that data separated by /. How can I separate this column using the delimiter?

Upvotes: 3

Views: 37829

Answers (6)

Balaji
Balaji

Reputation: 112

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)

Upvotes: 0

wumpz
wumpz

Reputation: 9131

Assuming your database column account.date contains a valid datetime or date value you should using SQLServers date functions like:

select month(getDate()) as "Month", 
       day(getDate()) as "Day", 
       year(getDate()) as "Year"

I replaced your column account.date by a getDate() to have some test values. This maps to your SQL in the following way

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.

If you have indeed varchar values there are several SO questions like: How do I split a string so I can access item x?.

Upvotes: 0

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

Surprisingly CAST('2/November/2000' as datetime) works (checked on SQL Server 2008), gives value 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?

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 .

You should never store date values as strings.

Upvotes: 4

GarethD
GarethD

Reputation: 69759

You can abuse the PARSENAME function slightly here:

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_CONVERT function which can make such conversions easier, but you still need to know what format your string date is in, 2/11/2000 could be the 2nd November, or 11th February depending on your regional settings.

Upvotes: 1

Szymon
Szymon

Reputation: 43023

You can do it this way by using CHARINDEX and SUBSTRING functions

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

Upvotes: 4

Nickolay Komar
Nickolay Komar

Reputation: 340

You can use the combination of CharIndex and Substring function in SQL to split the string based on delimiter.

You can check CharIndex examples here SQL Server 2005 Using CHARINDEX() To split a string and here SQL Server - find nth occurrence in a string

Upvotes: 0

Related Questions