Author |
Topic |
peace
Constraint Violating Yak Guru
420 Posts |
Posted - 2014-08-20 : 00:20:15
|
how to just grab in between data? dataID 1234 20/2/2014 0:0:0 UK AUS 4/8/2014 12:20:0ID 1343 1/8/2014 0:0:0 BOS AUS 23/8/2014 12:20:0ID 3434 3/8/2014 0:0:0 BOS UK 1/8/2014 12:20:0ID 4343 2/8/2014 0:0:0 BOS PH 24/8/2014 12:20:0ID 2322 4/8/2014 0:0:0 ARG AUS 20/8/2014 12:20:0Just want to grab country.newFieldUK AUSBOS AUSBOS UK'''but the data before and after country code has different ID and datetime for every each data. |
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2014-08-20 : 00:57:40
|
This is one way to do it:select * ,substring(yourdatafield ,charindex(' ' ,yourdatafield ,charindex(' ' ,yourdatafield ,charindex(' ' ,yourdatafield ,charindex(' ' ,yourdatafield )+1 )+1 )+1 )+1 ,charindex(' ' ,yourdatafield ,charindex(' ' ,yourdatafield ,charindex(' ' ,yourdatafield ,charindex(' ' ,yourdatafield ,charindex(' ' ,yourdatafield ,charindex(' ' ,yourdatafield )+1 )+1 )+1 )+1 )+1 ) -charindex(' ' ,yourdatafield ,charindex(' ' ,yourdatafield ,charindex(' ' ,yourdatafield ,charindex(' ' ,yourdatafield )+1 )+1 )+1 )-1 ) from yourtable |
|
|
peace
Constraint Violating Yak Guru
420 Posts |
Posted - 2014-08-20 : 01:54:14
|
is it possible to use something like [%0-9%] |
|
|
sz1
Aged Yak Warrior
555 Posts |
Posted - 2014-08-20 : 04:32:04
|
Am I missing something here? you mean you just want to pull the country field back? what else do you want the query to do? what are your table headings?We are the creators of our own reality! |
|
|
peace
Constraint Violating Yak Guru
420 Posts |
Posted - 2014-08-20 : 04:53:47
|
Yes, just want to pull the country. but the date time always change. |
|
|
sz1
Aged Yak Warrior
555 Posts |
Posted - 2014-08-20 : 05:06:46
|
What does it matter if the dates and times are different unless you want to query specific datetimes? Surely you need more to the query than this?Select CountryFrom YourTableOrder By Country ASCUsing where:Select CountryFrom YourTableWhere Country = 'UK AUS'Order By Country ASCWe are the creators of our own reality! |
|
|
peace
Constraint Violating Yak Guru
420 Posts |
Posted - 2014-08-20 : 05:10:47
|
The data look like this:dataID 1234 20/2/2014 0:0:0 UK AUS 4/8/2014 12:20:0ID 1343 1/8/2014 0:0:0 BOS AUS 23/8/2014 12:20:0ID 3434 3/8/2014 0:0:0 BOS UK 1/8/2014 12:20:0ID 4343 2/8/2014 0:0:0 BOS PH 24/8/2014 12:20:0ID 2322 4/8/2014 0:0:0 ARG AUS 20/8/2014 12:20:0I just want to grab the country in between the strings.newFieldUK AUSBOS AUSBOS UK''' |
|
|
sz1
Aged Yak Warrior
555 Posts |
Posted - 2014-08-20 : 05:13:35
|
What file format is the data in? this isn't a SQL table is it? is it a CSV file?We are the creators of our own reality! |
|
|
peace
Constraint Violating Yak Guru
420 Posts |
Posted - 2014-08-20 : 05:15:19
|
i query from my table.. |
|
|
sz1
Aged Yak Warrior
555 Posts |
Posted - 2014-08-20 : 05:18:21
|
Ok, what is your table called and what are your table headings?We are the creators of our own reality! |
|
|
peace
Constraint Violating Yak Guru
420 Posts |
Posted - 2014-08-20 : 05:22:45
|
as in earlier request..select data from tableAdataID 1234 20/2/2014 0:0:0 UK AUS 4/8/2014 12:20:0ID 1343 1/8/2014 0:0:0 BOS AUS 23/8/2014 12:20:0ID 3434 3/8/2014 0:0:0 BOS UK 1/8/2014 12:20:0ID 4343 2/8/2014 0:0:0 BOS PH 24/8/2014 12:20:0ID 2322 4/8/2014 0:0:0 ARG AUS 20/8/2014 12:20:0wanted to just pull the code in between..newFieldUK AUSBOS AUSBOS UK''' |
|
|
sz1
Aged Yak Warrior
555 Posts |
Posted - 2014-08-20 : 06:47:48
|
Agh first of all the data has been fetched in badly, everything has been dropped into one column which is really bad. Having said that you can still query the text string single column whith various functions for example: create table #stringsearch ( data varchar(MAX) ) Insert into #stringsearch Values ('ID 1234 20/2/2014 0:0:0 UK AUS 4/8/2014 12:20:0'),('ID 1343 1/8/2014 0:0:0 BOS AUS 23/8/2014 12:20:0'),('ID 3434 3/8/2014 0:0:0 BOS UK 1/8/2014 12:20:0'),('ID 4343 2/8/2014 0:0:0 BOS PH 24/8/2014 12:20:0'),('ID 2322 4/8/2014 0:0:0 ARG AUS 20/8/2014 12:20:0')select * from #stringsearchSelect SUBSTRING(data,24,7)From #stringsearchOr in your case:Select SUBSTRING(data,24,7) as CountryFrom TableAResult:Country UK AUSBOS AUSBOS UK BOS PH ARG AUSWe are the creators of our own reality! |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2014-08-20 : 06:55:54
|
Something like below may workdeclare @data table(data varchar(1000))insert into @dataselect 'ID 1234 20/2/2014 0:0:0 UK AUS 4/8/2014 12:20:0' union allselect 'ID 1343 1/8/2014 0:0:0 BOS AUS 23/8/2014 12:20:0' union allselect 'ID 3434 3/8/2014 0:0:0 BOS UK 1/8/2014 12:20:0' union allselect 'ID 4343 2/8/2014 0:0:0 BOS PH 24/8/2014 12:20:0' union allselect 'ID 2322 4/8/2014 0:0:0 ARG AUS 20/8/2014 12:20:0'select substring(data, 1,patindex('%[0-9]%',data)-2) as data from( select substring(data, patindex('%/[0-9][0-9][0-9][0-9]%',data)+12,len(data)) as data from @data) as t Resultdata-----------------------UK AUSBOS AUSBOS UKBOS PHARG AUS MadhivananFailing to plan is Planning to fail |
|
|
peace
Constraint Violating Yak Guru
420 Posts |
Posted - 2014-08-20 : 09:40:31
|
Thanks will try out. |
|
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2014-08-20 : 12:20:24
|
Another alternative:select * ,left(right(yourdatafield ,len(yourdatafield) -patindex('% [A-Z][A-Z]% [A-Z][A-Z]%' ,yourdatafield ) ) ,patindex('% [0-9]%' ,right(yourdatafield ,len(yourdatafield) -patindex('% [A-Z][A-Z]% [A-Z][A-Z]%' ,yourdatafield ) ) )-1 ) from yourtable |
|
|
peace
Constraint Violating Yak Guru
420 Posts |
Posted - 2014-08-20 : 23:43:07
|
Thanks for the samples.I tried this method, it works well.declare @data table(data varchar(1000))insert into @dataselect 'ID 1234 20/2/2014 0:0:0 UK AUS 4/8/2014 12:20:0' union allselect 'ID 1343 1/8/2014 0:0:0 BOS AUS 23/8/2014 12:20:0' union allselect 'ID 3434 3/8/2014 0:0:0 BOS UK 1/8/2014 12:20:0' union allselect 'ID 4343 2/8/2014 0:0:0 BOS PH 24/8/2014 12:20:0' union allselect 'ID 2322 4/8/2014 0:0:0 ARG AUS 20/8/2014 12:20:0'select substring(data, 1,patindex('%[0-9]%',data)-2) as data from( select substring(data, patindex('%/[0-9][0-9][0-9][0-9]%',data)+12,len(data)) as data from @data) as tFrom the Substring method, how to insert notes in between? i tried something like this not working.select data from tableAdataID 1234 20/2/2014 0:0:0 UK AUS 4/8/2014 12:20:0insert notes in between, i tried something like this:declare @1 as nvarchar(max)set @1='ID 1234 20/2/2014 0:0:0 UK AUS 4/8/2014 12:20:0'select 'Departure'+' '+substring(@1, patindex('%/[0-9][0-9][0-9][0-9]%',@1)+12,len(@1))how to insert in between the data? where to apply the + string? --take off and landedID 1234 TAKEOFF 20/2/2014 0:0:0 UK AUS LANDED 4/8/2014 12:20:0 |
|
|
|