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

SQL Server 2008 Forums

Transact-SQL (2008)

Query

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2010-06-04 : 11:04:58

[code]
I have the below data.

A00920100331AUD
U09JK20100330USD

and would require the below output..

A009|20100331
U09JK|20100330

Note:

I want to exclude the last 3 currency characters and also to include "|" before the datetime (ie 20100330 or 20100331).

Thanks for your help in advance.

[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-04 : 11:18:23

Here is one way

select data,substring(data,1,charindex(right(left(data,len(data)-3),8),data)-1)+'|'+ right(left(data,len(data)-3),8) from
(
select 'A00920100331AUD' as data union all
select 'U09JK20100330USD'
) as t

Madhivanan

Failing to plan is Planning to fail

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2010-06-04 : 11:22:13

Thanks Madhivanan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-04 : 11:27:48

quote:
Originally posted by sqlfresher2k7

Thanks Madhivanan

You are welcome

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources