January 25, 2011 at 12:44 pm
Hi,
I have a staging table e.g
TRITON9700 L9700031288172 10-07-09 I EDC TRITON 9700 CASH DISPENSER
all of them stored in one column.
I want to export these to excel . I want only the date "10-07-09" As [Date] and all the string after "I" as [Description].
How do I do it with T-SQL ?
Thanks.
January 25, 2011 at 1:44 pm
It depends.
If the date is always at the same position, you could use SUBSTRING to eliminate it. Otherwise you'd need to find the pipe character and move left.
As a side note: I hope you're talking about a pipe delimiter and not the character "I" like used in your example... Otherwise you'd need to make sure that ' I ' only occurs once at the specific position...
DECLARE @STR VARCHAR(200)
SET @STR='TRITON9700 L9700031288172 10-07-09 | EDC TRITON 9700 CASH DISPENSER'
SELECT
SUBSTRING(@str,27,8),
SUBSTRING(@str,CHARINDEX('|',@str)-9,8),
STUFF(@str,1,CHARINDEX('|',@str),'')
Edit: line feed added for reability
January 25, 2011 at 2:01 pm
Thanks. When I execute your query, I get two columns for date and then one for description.
I want just onle column for date. Also I is the character and not a pipe delimeter.It occurs only once . Always after date.
January 25, 2011 at 2:08 pm
Sometimes I might have this
TRITONRL5000 LRL5114070400714 10-19-10 I EDC TRITON RL5000 CASH DISPENSER
or
5305 32910394 10-06-09 I EDC NCR MCD CASH DISPENSER
January 25, 2011 at 2:18 pm
If you need to separate the string based on a specific set of character (e.g. '_I_') you should look into PATINDEX and '% I %' instead of CHARINDEX and the pipe delimiter.
Regarding the two dates you receive when running my query: Look at the different ways used to get the date value and understand how both work.
[SARCASM ON] I'm just not in the spoon feeding mood right now...[SARCASM OFF] 😎
January 25, 2011 at 5:21 pm
PSB (1/25/2011)
Sometimes I might have thisTRITONRL5000 LRL5114070400714 10-19-10 I EDC TRITON RL5000 CASH DISPENSER
or
5305 32910394 10-06-09 I EDC NCR MCD CASH DISPENSER
So, is the date always after the second space?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
January 25, 2011 at 8:22 pm
Does the following snippit help?
declare @TestStr varchar(128) = 'TRITON9700 L9700031288172 10-07-09 | EDC TRITON 9700 CASH DISPENSER';
select
SUBSTRING(@TestStr, PATINDEX('%[0-9][0-9]-[0-9][0-9]-[0-9][0-9]%|%',@TestStr), 8) as CharDate,
CAST(SUBSTRING(@TestStr, PATINDEX('%[0-9][0-9]-[0-9][0-9]-[0-9][0-9]%|%',@TestStr), 8) as datetime) as DateVal,
RIGHT(@TestStr, LEN(@TestStr) - PATINDEX('%|%', @TestStr)) as ItemDescription;
January 26, 2011 at 12:09 am
Lynn Pettis (1/25/2011)
Does the following snippit help?
declare @TestStr varchar(128) = 'TRITON9700 L9700031288172 10-07-09 | EDC TRITON 9700 CASH DISPENSER';
select
SUBSTRING(@TestStr, PATINDEX('%[0-9][0-9]-[0-9][0-9]-[0-9][0-9]%|%',@TestStr), 8) as CharDate,
CAST(SUBSTRING(@TestStr, PATINDEX('%[0-9][0-9]-[0-9][0-9]-[0-9][0-9]%|%',@TestStr), 8) as datetime) as DateVal,
RIGHT(@TestStr, LEN(@TestStr) - PATINDEX('%|%', @TestStr)) as ItemDescription;
Nice.... I was heading down the approach to look for the second space.
Good job Lynn! (you still know how to do it, even if you are working with Oracle now :w00t::-D)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
January 26, 2011 at 12:27 am
WayneS (1/26/2011)
Lynn Pettis (1/25/2011)
Does the following snippit help?
declare @TestStr varchar(128) = 'TRITON9700 L9700031288172 10-07-09 | EDC TRITON 9700 CASH DISPENSER';
select
SUBSTRING(@TestStr, PATINDEX('%[0-9][0-9]-[0-9][0-9]-[0-9][0-9]%|%',@TestStr), 8) as CharDate,
CAST(SUBSTRING(@TestStr, PATINDEX('%[0-9][0-9]-[0-9][0-9]-[0-9][0-9]%|%',@TestStr), 8) as datetime) as DateVal,
RIGHT(@TestStr, LEN(@TestStr) - PATINDEX('%|%', @TestStr)) as ItemDescription;
Nice.... I was heading down the approach to look for the second space.
Good job Lynn! (you still know how to do it, even if you are working with Oracle now :w00t::-D)
I may be working with Oracle but I will not surrender to the Dark Side! My first love (when it comes to database stuff) will always be SQL Server.
January 26, 2011 at 2:46 pm
It fails with string
5305 32910394 10-06-09 I EDC NCR MCD CASH DISPENSER
and
5670FL 26516262 11-09-09 I EDC FRONT LOAD LOBBY CASH DISPENSER
January 26, 2011 at 3:09 pm
"It fails" is a typical user response. How did it fail, what is the exact error message or messages you are getting.
If you would like better help, help us. We need the DDL for the table(s) (CREATE TABLE statement(s)), sample data for the table(s), what code you have currently tried to solve your problem, and finally we need the expected results based on the sample data you provide.
For help with most of this, please read the first article I reference below in my signature block.
January 26, 2011 at 3:10 pm
PSB (1/26/2011)
It fails with string5305 32910394 10-06-09 I EDC NCR MCD CASH DISPENSER
and
5670FL 26516262 11-09-09 I EDC FRONT LOAD LOBBY CASH DISPENSER
Weird.
Both solutions (Lynns and mine) return the expected result for the two sample rows you provided (assuming you implemeted the changes required to search for the character "I" instead of a pipe).
Since the character 'I' only occures onece (as per the requirement stated previously, expanded by adding a space befoer and after), I don't think the format check inside the PATINDEX is required.
I'd also be curious regarding the difference (if there's any at all) between your approach to use RIGHT(LEN()-...) vs. the STUFF() approach I recommended. But IIRC the difference is negligible.
January 26, 2011 at 9:02 pm
LutzM (1/26/2011)
PSB (1/26/2011)
It fails with string5305 32910394 10-06-09 I EDC NCR MCD CASH DISPENSER
and
5670FL 26516262 11-09-09 I EDC FRONT LOAD LOBBY CASH DISPENSER
Weird.
Both solutions (Lynns and mine) return the expected result for the two sample rows you provided (assuming you implemeted the changes required to search for the character "I" instead of a pipe).
Since the character 'I' only occures onece (as per the requirement stated previously, expanded by adding a space befoer and after), I don't think the format check inside the PATINDEX is required.
I'd also be curious regarding the difference (if there's any at all) between your approach to use RIGHT(LEN()-...) vs. the STUFF() approach I recommended. But IIRC the difference is negligible.
Lutz, Testing is the only way to know, and the million row test would be the way to go. We just need to decide who will do the testing.
PSB, Are you going to provide us with the error information so we can try and help?
January 27, 2011 at 8:03 am
If I use this string below
DECLARE @TestStr VARCHAR(MAX)
SET @TestStr=' 5305 32909033 10-06-09 I EDC NCR MCD CASH DISPENSER'
select
SUBSTRING(@TestStr, PATINDEX('%[0-9][0-9]-[0-9][0-9]-[0-9][0-9]%|%',@TestStr), 8) as CharDate,
CAST(SUBSTRING(@TestStr, PATINDEX('%[0-9][0-9]-[0-9][0-9]-[0-9][0-9]%|%',@TestStr), 8) as datetime) as DateVal,
RIGHT(@TestStr, LEN(@TestStr) - PATINDEX('%|%', @TestStr)) as ItemDescription;
I get the below result:
CharDate DateVal ItemDescription
5305 5305-01-01 00:00:00.000 5305 32909033 10-06-09 I EDC NCR MCD CASH DISPENSER
Instead of
CharDate DateVal ItemDescription
10-07-09 2009-10-07 00:00:00.000 EDC TRITON 9700 CASH DISPENSER
January 27, 2011 at 10:51 am
@PSB:
Did you actually read my previous reply? My comment stands as it is. Reposting it won't change it.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply