substring - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

substring

Author

Topic

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2014-08-20 : 00:20:15

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
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 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!

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2014-08-20 : 05:10:47

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
'
'
'

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 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
'
'
'

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 #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!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2014-08-20 : 06:55:54

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

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 @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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources