Data Formating

I have a column 'Msg' in a table and test data looks like this:
& #123;1:@.:20:040705@.:21B:XXXX@.:33A:040705
XXX00000,@.:55D:111XXX@.MYE@.-}
& #123;2:@.:20:040705@.:21B:XXXX@.:33A:040705
XXX00000,@.:55D:111XXX@.MYE@.-}
& #123;6:@.:20:040705@.:21B:XXXX@.:33A:040705
XXX00000,@.:55D:111XXX@.MYE@.-}
My requirement is to reformat and reproduce the above lines. Find '@.' and
return to next line. '@.' should be suppressed. Example:
{1:
:20:040705
:21B:XXXX
:33A:040705XXX00000,
:55D:111XXX
MYE
-}
{2:
:20:040705
:21B:XXXX
:33A:040705XXX00000,
:55D:111XXX
MYE
-}
{6:
:20:040705
:21B:XXXX
:33A:040705XXX00000,
:55D:111XXX
MYE
-}Try repacing '@.' by char(13) + char(10) using function replace.
Example:
use northwind
go
select
replace(c1, '@.', char(13) + char(10)) as c
from
(
select '& #123;1:@.:20:040705@.:21B:XXXX@.:33A:040705
XXX00000,@.:55D:111XXX@.MYE@.-
}'
as c1
union all
select '& #123;2:@.:20:040705@.:21B:XXXX@.:33A:040705
XXX00000,@.:55D:111XXX@.MYE@.-
}'
union all
select '& #123;6:@.:20:040705@.:21B:XXXX@.:33A:040705
XXX00000,@.:55D:111XXX@.MYE@.-
}'
) as t1
AMB
"Fraz" wrote:

> I have a column 'Msg' in a table and test data looks like this:
> & #123;1:@.:20:040705@.:21B:XXXX@.:33A:040705
XXX00000,@.:55D:111XXX@.MYE@.-}
> & #123;2:@.:20:040705@.:21B:XXXX@.:33A:040705
XXX00000,@.:55D:111XXX@.MYE@.-}
> & #123;6:@.:20:040705@.:21B:XXXX@.:33A:040705
XXX00000,@.:55D:111XXX@.MYE@.-}
> My requirement is to reformat and reproduce the above lines. Find '@.' and
> return to next line. '@.' should be suppressed. Example:
> {1:
> :20:040705
> :21B:XXXX
> :33A:040705XXX00000,
> :55D:111XXX
> MYE
> -}
> {2:
> :20:040705
> :21B:XXXX
> :33A:040705XXX00000,
> :55D:111XXX
> MYE
> -}
> {6:
> :20:040705
> :21B:XXXX
> :33A:040705XXX00000,
> :55D:111XXX
> MYE
> -}
>|||SELECT
SUBSTRING(Msg, 1, 3),
SUBSTRING(Msg, 5, 10),
SUBSTRING(Msg, 16, 9),
SUBSTRING(Msg, 26, 20),
SUBSTRING(Msg, 47, 10),
SUBSTRING(Msg, 59, 3),
SUBSTRING(Msg, 63, 2)
FROM Tbl
By the way, definitely would be better to do this on the client side.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Fraz" <Fraz@.discussions.microsoft.com> wrote in message
news:8EF70F80-C73B-4CD0-9A08-C62157FA5AA4@.microsoft.com...
> I have a column 'Msg' in a table and test data looks like this:
> & #123;1:@.:20:040705@.:21B:XXXX@.:33A:040705
XXX00000,@.:55D:111XXX@.MYE@.-}
> & #123;2:@.:20:040705@.:21B:XXXX@.:33A:040705
XXX00000,@.:55D:111XXX@.MYE@.-}
> & #123;6:@.:20:040705@.:21B:XXXX@.:33A:040705
XXX00000,@.:55D:111XXX@.MYE@.-}
> My requirement is to reformat and reproduce the above lines. Find '@.' and
> return to next line. '@.' should be suppressed. Example:
> {1:
> :20:040705
> :21B:XXXX
> :33A:040705XXX00000,
> :55D:111XXX
> MYE
> -}
> {2:
> :20:040705
> :21B:XXXX
> :33A:040705XXX00000,
> :55D:111XXX
> MYE
> -}
> {6:
> :20:040705
> :21B:XXXX
> :33A:040705XXX00000,
> :55D:111XXX
> MYE
> -}
>|||Thanks for the quick response Alejandro. As I have more than 1000 rows in th
e
table, it will be tedious for me. Secondly the positon of '@.' in the row is
not fixed so substring might not work. I was trying to create a loop to
search for '@.' and return to next line. Now I am trying to work on Replace
function. Thanks...Fraz
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Try repacing '@.' by char(13) + char(10) using function replace.
> Example:
> use northwind
> go
> select
> replace(c1, '@.', char(13) + char(10)) as c
> from
> (
> select '& #123;1:@.:20:040705@.:21B:XXXX@.:33A:040705
XXX00000,@.:55D:111XXX@.MY
E@.-}'
> as c1
> union all
> select '& #123;2:@.:20:040705@.:21B:XXXX@.:33A:040705
XXX00000,@.:55D:111XXX@.MY
E@.-}'
> union all
> select '& #123;6:@.:20:040705@.:21B:XXXX@.:33A:040705
XXX00000,@.:55D:111XXX@.MY
E@.-}'
> ) as t1
>
> AMB
> "Fraz" wrote:
>|||The following statement gave the desired results. Thanks for all your help.
SELECT replace(Msg, '@.', CHAR(13)) FROM MsgTable
Fraz.
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Try repacing '@.' by char(13) + char(10) using function replace.
> Example:
> use northwind
> go
> select
> replace(c1, '@.', char(13) + char(10)) as c
> from
> (
> select '& #123;1:@.:20:040705@.:21B:XXXX@.:33A:040705
XXX00000,@.:55D:111XXX@.MY
E@.-}'
> as c1
> union all
> select '& #123;2:@.:20:040705@.:21B:XXXX@.:33A:040705
XXX00000,@.:55D:111XXX@.MY
E@.-}'
> union all
> select '& #123;6:@.:20:040705@.:21B:XXXX@.:33A:040705
XXX00000,@.:55D:111XXX@.MY
E@.-}'
> ) as t1
>
> AMB
> "Fraz" wrote:
>|||Fraz,
I thought you were talking about the internal format of the column value,
but it seems that you want to generate a row for each substring. If I
understood now, the best way should be using an auxiliary number table.
Why should I consider using an auxiliary numbers table?
http://www.aspfaq.com/show.asp?id=2516
Example:
use northwind
go
create table t1 (
c1 int not null identity,
c2 varchar(4000)
)
go
select
identity(int, 1, 1) as number
into
number
from
sysobjects as a
cross join
sysobjects as b
go
insert into t1 (c2)
select
c2
from
(
select '& #123;1:@.:20:040705@.:21B:XXXX@.:33A:040705
XXX00000,@.:55D:111XXX@.MYE@.-
}'
as c2
union all
select '& #123;2:@.:20:040705@.:21B:XXXX@.:33A:040705
XXX00000,@.:55D:111XXX@.MYE@.-
}'
union all
select '& #123;6:@.:20:040705@.:21B:XXXX@.:33A:040705
XXX00000,@.:55D:111XXX@.MYE@.-
}'
) as t1
go
select
substring('@.' + t1.c2 + '@.', n.number + 1, charindex('@.', '@.' + t1.c2 +
'@.', n.number + 1) - n.number - 1)
from
t1
inner join
number as n
on substring('@.' + t1.c2 + '@.', n.number, 1) = '@.'
and n.number < datalength('@.' + t1.c2 + '@.')
order by
t1.c1,
n.number
go
drop table number, t1
go
AMB
"Fraz" wrote:
[vbcol=seagreen]
> Thanks for the quick response Alejandro. As I have more than 1000 rows in
the
> table, it will be tedious for me. Secondly the positon of '@.' in the row i
s
> not fixed so substring might not work. I was trying to create a loop to
> search for '@.' and return to next line. Now I am trying to work on Replace
> function. Thanks...Fraz
> "Alejandro Mesa" wrote:
>|||Given you have an unknown and varialbe count of '@.'s per message line, you
will probably need a stored procedure something like
declare searcher varchar(2000)
declare @.here integer
declare fred scroll cursor for select msg from table
select @.here=0
open fred
fetch next from fred into searcher
while @.@.fetch_status=0
begin
while @.here<len(@.searcher)
begin
print substr(searcher,@.here,charindex('@.',sear
cher))
select @.here=charindex('@.',searcher)
if @.here=0 then select @.here=len(searchere)+1
end
select @.here=0
fetch next from fred into searcher
end
Joseph R.P. Maloney, CSP,CCP,CDP
"Fraz" wrote:

> I have a column 'Msg' in a table and test data looks like this:
> & #123;1:@.:20:040705@.:21B:XXXX@.:33A:040705
XXX00000,@.:55D:111XXX@.MYE@.-}
> & #123;2:@.:20:040705@.:21B:XXXX@.:33A:040705
XXX00000,@.:55D:111XXX@.MYE@.-}
> & #123;6:@.:20:040705@.:21B:XXXX@.:33A:040705
XXX00000,@.:55D:111XXX@.MYE@.-}
> My requirement is to reformat and reproduce the above lines. Find '@.' and
> return to next line. '@.' should be suppressed. Example:
> {1:
> :20:040705
> :21B:XXXX
> :33A:040705XXX00000,
> :55D:111XXX
> MYE
> -}
> {2:
> :20:040705
> :21B:XXXX
> :33A:040705XXX00000,
> :55D:111XXX
> MYE
> -}
> {6:
> :20:040705
> :21B:XXXX
> :33A:040705XXX00000,
> :55D:111XXX
> MYE
> -}
>