I try to split this string 3/9/1395 12:00:00 AM and get this string 1395/9/3 .I search but sql has no method for this work. How to do this?
-
I suppose your DBMS is Sql Server, right?Joe Taras– Joe Taras2016-06-16 08:17:44 +00:00Commented Jun 16, 2016 at 8:17
-
Different databases will deal with this differently. Are you using Microsoft SQL Server or MySQL or Oracle for example? Once we know the system you're using it will help you get better answersRich Benner– Rich Benner2016-06-16 08:36:25 +00:00Commented Jun 16, 2016 at 8:36
Add a comment
|
4 Answers
You can convert your variable to a Date and get Year, Month and Day
DATEPART(YEAR,Cast('3/9/1395 12:00:00 AM' as date))
DATEPART(MONTH,Cast('3/9/1395 12:00:00 AM' as date))
DATEPART(DAY,Cast('3/9/1395 12:00:00 AM' as date))
Comments
Ok, this is a really hacky way of doing this and would work for values that aren't dates that you need to split. There's easier ways of answering this particular question as it's to do with dates but for general string splitting you can take this approach;
Test Data;
CREATE TABLE #TestData (OriginalString varchar(22))
INSERT INTO #TestData (OriginalString)
VALUES
('3/9/1395 12:00:00 AM')
,('10/9/1462 04:00:00 AM')
,('25/12/1900 15:00:00 PM')
,('3/11/1264 13:00:00 PM')
Result Query;
SELECT
OriginalString
,RIGHT(LEFT(OriginalString,CHARINDEX(' ',OriginalString,1)-1),4) + '/' + RIGHT(LEFT(OriginalString,CHARINDEX('/',OriginalString,CHARINDEX('/',OriginalString,1)+1)-1),LEN(LEFT(OriginalString,CHARINDEX('/',OriginalString,CHARINDEX('/',OriginalString,1)+1)-1))-CHARINDEX('/',OriginalString,1)) + '/' + LEFT(OriginalString,CHARINDEX('/',OriginalString,1)-1) Result
FROM #TestData
Which gives these results;
OriginalString Result
3/9/1395 12:00:00 AM 1395/9/3
10/9/1462 04:00:00 AM 1462/9/10
25/12/1900 15:00:00 PM 1900/12/25
3/11/1264 13:00:00 PM 1264/11/3
To get to this result, I built the result part by part and worked from there, if you take a look at the following query you can see how I came to this answer, it may help you if you need to do this again in future;
SELECT
OriginalString
,CHARINDEX('/',OriginalString,1) FirstSlashLocation
,LEFT(OriginalString,CHARINDEX('/',OriginalString,1)-1) FirstValue
,CHARINDEX('/',OriginalString,CHARINDEX('/',OriginalString,1)+1) SecondSlashLocation
,RIGHT(LEFT(OriginalString,CHARINDEX('/',OriginalString,CHARINDEX('/',OriginalString,1)+1)-1),LEN(LEFT(OriginalString,CHARINDEX('/',OriginalString,CHARINDEX('/',OriginalString,1)+1)-1))-CHARINDEX('/',OriginalString,1)) SecondValue
,CHARINDEX(' ',OriginalString,1) SpaceLocation
,RIGHT(LEFT(OriginalString,CHARINDEX(' ',OriginalString,1)-1),4) FinalValue
,RIGHT(LEFT(OriginalString,CHARINDEX(' ',OriginalString,1)-1),4) + '/' + RIGHT(LEFT(OriginalString,CHARINDEX('/',OriginalString,CHARINDEX('/',OriginalString,1)+1)-1),LEN(LEFT(OriginalString,CHARINDEX('/',OriginalString,CHARINDEX('/',OriginalString,1)+1)-1))-CHARINDEX('/',OriginalString,1)) + '/' + LEFT(OriginalString,CHARINDEX('/',OriginalString,1)-1) Result
FROM #TestData
Which gives these results;
OriginalString FirstSlashLocation FirstValue SecondSlashLocation SecondValue SpaceLocation FinalValue Result
3/9/1395 12:00:00 AM 2 3 4 9 9 1395 1395/9/3
10/9/1462 04:00:00 AM 3 10 5 9 10 1462 1462/9/10
25/12/1900 15:00:00 PM 3 25 6 12 11 1900 1900/12/25
3/11/1264 13:00:00 PM 2 3 5 11 10 1264 1264/11/3
Comments
If your DBMS is Sql Server (I suppose becuase in your title you've written Tsql)
Try this:
select
substring('3/9/1395 12:00:00 AM', 0,
charindex(' ', '3/9/1395 12:00:00 AM')
)
Your pattern is always date space time, so you'll get the first space position (with charindex function) and the you apply a substring function on your string
1 Comment
hmahdavi
This return 3/9/1395 but I want get 1395/9/3
If you column is date you could use
select CONVERT(VARCHAR(20),your_column , 110) from your_table
