August 19, 2014 at 1:51 am
i have a long list of string that look like this:
7/5/2014 0:0:0 AUS UK 7/5/2014 18:21:0 7/5/2014 20:45:0 7/5/2014 19:10:0 7/5/2014 23:30:0
how can i add in some indicator in between the data to indicate arrival and departure? is it possible?
7/5/2014 0:0:0 DEPARTURE AUS UK 7/5/2014 18:21:0 7/5/2014 20:45:0 ARRIVAL 7/5/2014 19:10:0 7/5/2014 23:30:0
Tried reading article on CHARINDEX, PATINDEX and SUBSTRING but get stuck. Any help?
August 19, 2014 at 1:57 am
girl_bj (8/19/2014)
i have a long list of string that look like this:7/5/2014 0:0:0 AUS UK 7/5/2014 18:21:0 7/5/2014 20:45:0 7/5/2014 19:10:0 7/5/2014 23:30:0
how can i add in some indicator in between the data to indicate arrival and departure? is it possible?
7/5/2014 0:0:0 DEPARTURE AUS UK 7/5/2014 18:21:0 7/5/2014 20:45:0 ARRIVAL 7/5/2014 19:10:0 7/5/2014 23:30:0
Tried reading article on CHARINDEX, PATINDEX and SUBSTRING but get stuck. Any help?
Certainly, it's possible. Where 'in between the data' do you want to insert such indicators, and what would the indicators look like?
August 19, 2014 at 2:08 am
7/5/2014 0:0:0 DEPARTURE AUS UK 7/5/2014 18:21:0 7/5/2014 20:45:0 ARRIVAL 7/5/2014 19:10:0 7/5/2014 23:30:0
that DEPARTURE and ARRIVAL
August 19, 2014 at 2:23 am
girl_bj (8/19/2014)
7/5/2014 0:0:0 DEPARTURE AUS UK 7/5/2014 18:21:0 7/5/2014 20:45:0 ARRIVAL 7/5/2014 19:10:0 7/5/2014 23:30:0that DEPARTURE and ARRIVAL
Okay, inserting such strings is trivial, but how do we determine WHERE to put them? I see:
[date] [time] [place] [place] [date] [time] [date] [time] [date] [time] [date] [time]
which you want to turn into:
[date] [time] DEPARTURE [place] [place] [date] [time] [date] [time] ARRIVAL [date] [time] [date] [time]
Isolating the elements is not trivial, but can be done. However, it's not clear from your example, where and how you want to do that. I can give you code that will do it in THIS exact case, but I assume that you want to solve a more general case. If so, we'll need a bit more information about how YOU, as a human, determined where you wanted to insert those strings, and how you will make such determinations in general.
August 19, 2014 at 2:40 am
i have a long list of data..will like to add in additional departure and arrival into the script..
7/5/2014 0:0:0 DEPARTURE AUS UK 7/5/2014 18:21:0 7/5/2014 20:45:0 ARRIVAL 7/5/2014 19:10:0 7/5/2014 23:30:0
7/5/2014 0:0:0 --date created
AUS UK 7/5/2014 18:21:0 7/5/2014 20:45:0 --it show the date and time of departure, old and new
7/5/2014 19:10:0 7/5/2014 23:30:0 --it show the date and time of arrival, old and new
basically to make the report looks more friendly to read, i would like to modify the scipt to add in the departure and arrival.
is that possible? as the date and time not always the same.
so will have new fields:
departure
AUS UK 7/5/2014 18:21:0 7/5/2014 20:45:0
arrival
7/5/2014 19:10:0 7/5/2014 23:30:0
August 19, 2014 at 2:45 am
girl_bj (8/19/2014)
i have a long list of data..will like to add in additional departure and arrival into the script..7/5/2014 0:0:0 DEPARTURE AUS UK 7/5/2014 18:21:0 7/5/2014 20:45:0 ARRIVAL 7/5/2014 19:10:0 7/5/2014 23:30:0
7/5/2014 0:0:0 --date created
AUS UK 7/5/2014 18:21:0 7/5/2014 20:45:0 --it show the date and time of departure, old and new
7/5/2014 19:10:0 7/5/2014 23:30:0 --it show the date and time of arrival, old and new
basically to make the report looks more friendly to read, i would like to modify the scipt to add in the departure and arrival.
is that possible? as the date and time not always the same.
so will have new fields:
departure
AUS UK 7/5/2014 18:21:0 7/5/2014 20:45:0
arrival
7/5/2014 19:10:0 7/5/2014 23:30:0
Yes, it's possible. You can dismantle such strings, but first, is this how you get the data, as one long string in this format? If you get the data as individual fields, then put them together into such a string, it will be simpler. If you get the data as one long string like this, it will require some string surgery, which is more difficult, but by no means impossible.
August 19, 2014 at 2:53 am
Originaly the data will be like this:
select top 1 batchRecord from tableA
batchRecord
7/5/2014 0:0:0 DEPARTURE AUS UK 7/5/2014 18:21:0 7/5/2014 20:45:0 ARRIVAL 7/5/2014 19:10:0 7/5/2014 23:30:0
August 19, 2014 at 3:09 am
Okay, here's something that should get you started. I'm assuming the everything will be in the form you posted here:
[date]<space>[time]<space>[place]<space>[place]<space>[date]<space>[time]<space>[date]<space>[time]<space>[date]<space>[time]<space>[date]<space>[time]
The code depends on these spaces to isolate the individual elements. There is possibly a bit more parsing than you need for this particular case, but it won't hurt anything, and may prove useful for other tasks, as well as clearly identifying the individual elements.
The print statements serve to show you each step in the process, so you will see how the input string is getting pared down, step by step.
One disclaimer: my use of varchar(max) may draw howls from some purists, but I use it as the equivalent of a Basic string variable. When you are dealing with actual table data, you should use appropriate sizes.
This code will run as a stand-alone block, so you can see what is going on. Extract whatever you need for your application, choosing only the proper pieces. For instance, you will certainly not want the print statements in your final product.
declare @InputString varchar(max)
declare @OutputString varchar(max)
set @InputString = '7/5/2014 0:0:0 AUS UK 7/5/2014 18:21:0 7/5/2014 20:45:0 7/5/2014 19:10:0 7/5/2014 23:30:0'
declare @DateTimeCreated varchar(max)
declare @Location varchar(max)
declare @DateTimeDepartureOld varchar(max)
declare @DateTimeDepartureNew varchar(max)
declare @DateTimeArrivalOld varchar(max)
declare @DateTimeArrivalNew varchar(max)
declare @i int
set @i = CHARINDEX(' ', @InputString, 1)
set @i = CHARINDEX(' ', @InputString, @i+1)
set @DateTimeCreated = LEFT(@InputString, @i-1)
set @InputString = SUBSTRING(@InputString, @i+1, 1000)
print @DateTimeCreated
print @InputString
print ''
set @i = CHARINDEX(' ', @InputString, 1)
set @i = CHARINDEX(' ', @InputString, @i+1)
set @Location = LEFT(@InputString, @i-1)
set @InputString = SUBSTRING(@InputString, @i+1, 1000)
print @Location
print @InputString
print ''
set @i = CHARINDEX(' ', @InputString, 1)
set @i = CHARINDEX(' ', @InputString, @i+1)
set @DateTimeDepartureOld = LEFT(@InputString, @i-1)
set @InputString = SUBSTRING(@InputString, @i+1, 1000)
print @DateTimeDepartureOld
print @InputString
print ''
set @i = CHARINDEX(' ', @InputString, 1)
set @i = CHARINDEX(' ', @InputString, @i+1)
set @DateTimeDepartureNew = LEFT(@InputString, @i-1)
set @InputString = SUBSTRING(@InputString, @i+1, 1000)
print @DateTimeDepartureNew
print @InputString
print ''
set @i = CHARINDEX(' ', @InputString, 1)
set @i = CHARINDEX(' ', @InputString, @i+1)
set @DateTimeArrivalOld = LEFT(@InputString, @i-1)
set @DateTimeArrivalNew = SUBSTRING(@InputString, @i+1, 1000)
print @DateTimeArrivalOld
print @DateTimeArrivalNew
print ''
set @OutputString = @DateTimeCreated + ' DEPARTURE ' + @Location + ' ' + @DateTimeDepartureOld + ' ' + @DateTimeDepartureNew + ' ARRIVAL ' + @DateTimeArrivalOld + ' ' + @DateTimeArrivalNew
print @OutputString
August 19, 2014 at 3:19 am
How to define to make it flexible on this part?
set @InputString = '7/5/2014 0:0:0 AUS UK 7/5/2014 18:21:0 7/5/2014 20:45:0 7/5/2014 19:10:0 7/5/2014 23:30:0'
Because I have a long list of data. The date, time and the country code is different on every each data.
August 19, 2014 at 3:32 am
girl_bj (8/19/2014)
How to define to make it flexible on this part?set @InputString = '7/5/2014 0:0:0 AUS UK 7/5/2014 18:21:0 7/5/2014 20:45:0 7/5/2014 19:10:0 7/5/2014 23:30:0'
Because I have a long list of data. The date, time and the country code is different on every each data.
This was just a literal, to show you the parsing technique. In your code, you would replace the line:
set @InputString = '7/5/2014 0:0:0 AUS UK 7/5/2014 18:21:0 7/5/2014 20:45:0 7/5/2014 19:10:0 7/5/2014 23:30:0'
by
set @InputString = (select top 1 batchRecord from tableA)
August 19, 2014 at 7:01 am
is there any shorter way like using substring method instead of set it?
August 19, 2014 at 7:12 am
girl_bj (8/19/2014)
is there any shorter way like using substring method instead of set it?
You can nest substring and charindex references directly to the original field, but that is definitely NOT simpler. Such a multiply nested set of references can get hairy in a big hurry - if you knew how to do that, you wouldn't have been posting the question in the first place.
This is about as simple as you can get. There are slight tweaks that you can do that will make it run marginally faster, but I did it this way to make the process as clear as possible, since you indicated that you didn't understand the string manipulation documentation. If you're not happy with this, I'm afraid I don't have much else to offer - definitely not anything simpler.
[edit]
On second though, there is one slight possibility - if the data was always, ALWAYS, ALWAYS exactly the same length, you could indeed use SubString, with hard-coded start and length values. But if the length of a piece of data varies, it will make such references incorrect. And since I see that you have dates and times without left padding, and therefore varying lengths, you can't use it here, unless you can get back to the source and force constant lengths.
August 19, 2014 at 9:04 pm
I get this error when i replace set the select statement.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
What it meant?
August 19, 2014 at 9:07 pm
Is it because i need to select only Top 1?
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply