Getting data from one column to write into 2 others

[Dabase]

Hi,

I have a column in my table with data in the following format:

Col05
-----
June 2005 xxx 15
May 2005 xxx 14
April 2005 xxx 11
March 2005 xxx 9

For each record, I need to write the Month and Year (eg. June 2005)into Col06 and the ID (eg. 15) into Col07. Is there a function (or any other method) that I can use to do this?



Thanks
Dabase

 

[SQLDenis]

create table Blahaha (value varchar(50))

insert into Blahaha
select 'June 2005 xxx 15' union all
select 'May 2005 xxx 14' union all
select 'April 2005 xxx 11' union all
select 'March 2005 xxx 9'


select left(value,charindex(' xxx',value)) as Col6,right(value,
len(value) -(charindex(' xxx',value) + len(' xxx'))) as col7,
* from Blahaha

or

select left(value,charindex(' xxx',value)) as Col6,right(value,
len(value) -(charindex(' xxx',value) + 4)) as col7,
* from Blahaha

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[hmckillop]

is the col05 in date format or the above format

try (based on above varchar format)

Update myTable
Set Col06 = left(col05, patindex('%xxx%', col05)-1), 
ltrim(right(col05, 2))

"I'm living so far beyond my income that we may almost be said to be living apart

 

[hmckillop]

sorry forgot the rest

Update myTable
Set Col06 = left(col05, patindex('%xxx%', col05)-1), 
col07 = ltrim(right(col05, 2))

"I'm living so far beyond my income that we may almost be said to be living apart

 

[vongrunt]

update blah set Col06 = left(Col05, patindex ('%[0-9] %', Col05))
update blah set Col07 = substring( Col05, Len(Col06)+2, 3)

... no matter what 'xxx' actually contains, as long as it is 3 characters wide.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[adamroof]

Run this in Query Analyzer to get the jist...
Obviously when you go live, you dont need to create a table, and subsititue your own fieldnames.

In a nutshell, find the first space index and increase by 5 to pass by the year

CREATE TABLE #temp (testval varchar(80),newval varchar(80) null,newval2 varchar(80) null)
INSERT INTO #temp (testval) VALUES ('june 2005 bla bla')
INSERT INTO #temp (testval) VALUES ('may 2005 bla2 bla2')

/* Update the destination records */
UPDATE #temp SET newval = (LTRIM(SUBSTRING(testval,0,CHARINDEX(' ',testval,1)+5))),
newval2 = (LTRIM(SUBSTRING(testval,CHARINDEX(' ',testval,1)+5,LEN(testval))))

/* Just To Show the records,might want to do this before the update command when live */
SELECT *,(LTRIM(SUBSTRING(testval,0,CHARINDEX(' ',testval,1)+5))) As test,
(LTRIM(SUBSTRING(testval,CHARINDEX(' ',testval,1)+5,LEN(testval)))) As test2
from #temp

RESULTS
----------
testval            newval     newval2   test1     test2
-------            ------     -------   -----     ------
june 2005 bla bla  june 2005  bla bla   june 2005 bla bla
may 2005 bla2 bla2 may 2005   bla2 bla2 may 2005  bla2 bla2

 

[adamroof]

wow, so many replies, before i finished mine... sorry for the dups, i swear there were no replies when i started to.

 

[Dabase]

Hi all,

I have used the following:
UPDATE MyTable
SET Col06 = rtrim(ltrim(parsename(replace(Col05, 'xxx', '.'),2))),
Col07 = rtrim(ltrim(parsename(replace(Col05, 'xxx', '.'),1)))

This gave me the results that I was looking for.

Thank you all for your quick replies.

Thanks
Dabase

 

[vongrunt]

Heh, parsename() is kinda popular these days

Side note: I thought you want update Col07 with 'xxx' token - my 2nd update doesn't work as specified. Gotta start reading posts from top to bottom... .

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)