replace and substring

[peac3]

Hi guys,

I would like to convert all these below :

C00920100331AUD
W09JK20100330USD

to become

C009|20100331
W09JK|20100330

so I would like to delete the last 3 currency characters, and add "|" before the datetime (ie 20100330 or 20100331)

Thanks guys,

 

[simian336]

update table
set [field] = Left([field],4) + '|' + substring([field],5,8)

Simi

 

[peac3]

Hi simian336,

Thanks for the post but the characters before date is not always 4 characters.

Cheers,

 

[simian336]

Does the year alway start with 2010?

Simi

 

[simian336]

try

declare @test as varchar(50)
set @test = 'C00920100331AUD'

select charindex('2010',@test)

select Left(@test,charindex('2010',@test)-1) + '|' + substring(@test,5,8)

Simi

 

[Crowley16]

is the date format consistent?

if so, then you could go the other way, strip out the end 3, then the date, then what's left...

something like:

substring(
col
, len(col) - 8 - 3 --date and country
, len(col) - 3 --country
) as date

left( len(col) - 8 - 3 ) as code

--------------------
Procrastinate Now!

 

[peac3]

Hi guys,

Thanks and I shouldve explained everything from beginning,

the reason I asked from conversion because I would like to join two tables using these column.

one column of one table contains :
C00920100331AUD
W09JK20100330USD
...

and other column of other table contains :
C009|20100331
W09JK|20100330
...

So I could not use declare for select statement.
I was using substring the last 3 characters and replace 2010 with |2010,

And I was hoping you guys can come up with other ideas.

Cheers,

 

[philhege]

This works with the current year. You can replace getdate() with the year you want to manipulate.

select replace(reverse(substring(reverse(MyConcatenatedField),4,len(MyConcatenatedField))),cast(year(getdate()) as varchar),
'|'+cast(year(getdate()) as varchar))

HTH,

-------++NO CARRIER++-------

 

[philhege]

Sorry, I meant "you can replace year(getdate())...."

-------++NO CARRIER++-------