SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 substring
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

peace
Constraint Violating Yak Guru

410 Posts

Posted - 08/20/2014 :  00:20:15  Show Profile  Reply with Quote
how to just grab in between data?

data
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

Just want to grab country.

newField
UK AUS
BOS AUS
BOS UK
'
'
'

but the data before and after country code has different ID and datetime for every each data.

bitsmed
Constraint Violating Yak Guru

405 Posts

Posted - 08/20/2014 :  00:57:40  Show Profile  Reply with Quote
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
Go to Top of Page

peace
Constraint Violating Yak Guru

410 Posts

Posted - 08/20/2014 :  01:54:14  Show Profile  Reply with Quote
is it possible to use something like [%0-9%]
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
443 Posts

Posted - 08/20/2014 :  04:32:04  Show Profile  Reply with Quote
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!
Go to Top of Page

peace
Constraint Violating Yak Guru

410 Posts

Posted - 08/20/2014 :  04:53:47  Show Profile  Reply with Quote
Yes, just want to pull the country. but the date time always change.
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
443 Posts

Posted - 08/20/2014 :  05:06:46  Show Profile  Reply with Quote
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 Country
From YourTable
Order By Country ASC

Using where:

Select Country
From YourTable
Where Country = 'UK AUS'
Order By Country ASC

We are the creators of our own reality!
Go to Top of Page

peace
Constraint Violating Yak Guru

410 Posts

Posted - 08/20/2014 :  05:10:47  Show Profile  Reply with Quote
The data look like this:

data
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

I just want to grab the country in between the strings.

newField
UK AUS
BOS AUS
BOS UK
'
'
'
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
443 Posts

Posted - 08/20/2014 :  05:13:35  Show Profile  Reply with Quote
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!
Go to Top of Page

peace
Constraint Violating Yak Guru

410 Posts

Posted - 08/20/2014 :  05:15:19  Show Profile  Reply with Quote
i query from my table..
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
443 Posts

Posted - 08/20/2014 :  05:18:21  Show Profile  Reply with Quote
Ok, what is your table called and what are your table headings?

We are the creators of our own reality!
Go to Top of Page

peace
Constraint Violating Yak Guru

410 Posts

Posted - 08/20/2014 :  05:22:45  Show Profile  Reply with Quote
as in earlier request..

select data from tableA

data
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

wanted to just pull the code in between..

newField
UK AUS
BOS AUS
BOS UK
'
'
'
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
443 Posts

Posted - 08/20/2014 :  06:47:48  Show Profile  Reply with Quote
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 #stringsearch

Select SUBSTRING(data,24,7)
From #stringsearch

Or in your case:

Select SUBSTRING(data,24,7) as Country
From TableA

Result:

Country
UK AUS
BOS AUS
BOS UK
BOS PH
ARG AUS

We are the creators of our own reality!
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 08/20/2014 :  06:55:54  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Something like below may work
declare @data table(data varchar(1000))
insert into @data
select 'ID 1234 20/2/2014 0:0:0 UK AUS 4/8/2014 12:20:0' union all
select 'ID 1343 1/8/2014 0:0:0 BOS AUS 23/8/2014 12:20:0' union all
select 'ID 3434 3/8/2014 0:0:0 BOS UK 1/8/2014 12:20:0' union all
select 'ID 4343 2/8/2014 0:0:0 BOS PH 24/8/2014 12:20:0' union all
select '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

Result

data
-----------------------
UK AUS
BOS AUS
BOS UK
BOS PH
ARG AUS



Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

peace
Constraint Violating Yak Guru

410 Posts

Posted - 08/20/2014 :  09:40:31  Show Profile  Reply with Quote
Thanks will try out.
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

405 Posts

Posted - 08/20/2014 :  12:20:24  Show Profile  Reply with Quote
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
Go to Top of Page

peace
Constraint Violating Yak Guru

410 Posts

Posted - 08/20/2014 :  23:43:07  Show Profile  Reply with Quote
Thanks for the samples.

I tried this method, it works well.

declare @data table(data varchar(1000))
insert into @data
select 'ID 1234 20/2/2014 0:0:0 UK AUS 4/8/2014 12:20:0' union all
select 'ID 1343 1/8/2014 0:0:0 BOS AUS 23/8/2014 12:20:0' union all
select 'ID 3434 3/8/2014 0:0:0 BOS UK 1/8/2014 12:20:0' union all
select 'ID 4343 2/8/2014 0:0:0 BOS PH 24/8/2014 12:20:0' union all
select '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

From the Substring method, how to insert notes in between? i tried something like this not working.

select data from tableA

data
ID 1234 20/2/2014 0:0:0 UK AUS 4/8/2014 12:20:0

insert 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 landed

ID 1234 TAKEOFF 20/2/2014 0:0:0 UK AUS LANDED 4/8/2014 12:20:0
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000