grab in between data - 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

grab in between data

Author

Topic

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2014-03-25 : 02:51:50

I have a list of data sample as below:

MYDATA
2/26/2014 19:35:0 2/26/2014 19:35:0 2/26/2014 22:25:0 UK AUS start end
3/1/2014 9:20:0 3/1/2014 9:20:0 3/1/2014 10:5:0 AUS UK start end

How can I only grab as below:

newColumn
UK AUS
AUS UK

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-03-25 : 05:27:27

This will answer what actually you need.......

CREATE TABLE Grab(NewColoumn VARCHAR(MAX))

INSERT INTO Grab VALUES ('2/26/2014 19:35:0 2/26/2014 19:35:0 2/26/2014 22:25:0 UK AUS start end')
,('3/1/2014 9:20:0 3/1/2014 9:20:0 3/1/2014 10:5:0 AUS UK start end')

WITH Example
AS
(
SELECT NewColoumn,Row_Number() OVER (ORDER BY NewColoumn) Rn FROM Grab

)
SELECT CASE WHEN Rn=1 THEN SUBSTRING(NewColoumn,CHARINDEX('U',NewColoumn),6)
WHEN Rn=2 THEN SUBSTRING(NewColoumn,CHARINDEX('A',NewColoumn),6)
END AS NewColoumn
FROM Example

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

Monib
Starting Member

11 Posts

Posted - 2014-03-25 : 06:16:12

Hi, If 'start End' in every record, then this will work fine.

SELECT REPLACE((right(Column_Name,16)),'start END',' ') AS Record FROM test_table

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2014-03-25 : 22:34:32

-it can be any alphabet as there're alot of data..

-some are not end with start end..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-25 : 22:47:22

can you explain what do you need ?

Please post your table schema DDL, sample data DML etc .. and any rules / logic / pattern in obtaining the required result.

Your initial post is too brief to understand what is that you required

KH
[spoiler]Time is always against us[/spoiler]

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2014-03-25 : 22:53:29

i have a list of data as below.

MYDATA
2/26/2014 19:35:0 2/26/2014 19:35:0 2/26/2014 22:25:0 UK AUS start end
3/1/2014 9:20:0 3/1/2014 9:20:0 3/1/2014 10:5:0 AUS UK start end
3/17/2014 4:20:0 9/1/2014 9:20:0 3/22/2014 15:5:0 CH CRK MS Hellen
'
'
'
'

How can I only grab as below:

newColumn
UK AUS
AUS UK
CH CRK

Grab the only country code after the time.

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-03-26 : 01:20:23

Here is your answer.....

CREATE TABLE Grab(NewColoumn VARCHAR(MAX))

INSERT INTO Grab VALUES ('2/26/2014 19:35:0 2/26/2014 19:35:0 2/26/2014 22:25:0 UK AUS start end')
,('3/1/2014 9:20:0 3/1/2014 9:20:0 3/1/2014 10:5:0 AUS UK start end')

SELECT SUBSTRING(NewColoumn,PATINDEX('%[A-Z]%',NewColoumn),6) As NewColumn
FROM Grab

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-26 : 01:23:20

- is it always 2 country code ?
- is it always the 3rd & 4th word from the right ?

KH
[spoiler]Time is always against us[/spoiler]

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2014-03-26 : 02:59:44

It works fine.

Thanks MuralikrishnaVeera

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources