loop through temporary table in SQL

I have a large table that looks like this.

(ID INT NOT NULL IDENTITY(1,1),PK INT , pocket VARCHAR(10))

1, 1, p1
2, 1, p2
3, 2, p3
4, 2, p4
5, 3, p5
6, 3, p6
7, 4, p7
8, 5, p1
9, 5, p2
10,5, p83

i would like to loop through the table and concatenate the pocket filed for all the records that has the same pk. and insert the pk and the concatenated string into another table in a timely manner.

can anyone help?

Jigsaw:

Do you want the concatenated list ordered? If so, how?

|||

Jigsaw:

Here is a way to do this using a CTE:

declare @.mockup table
(ID INT NOT NULL ,PK INT , pocket VARCHAR(10))

insert into @.mockup
select 1, 1, 'p1' union all
select 2, 1, 'p2' union all
select 3, 2, 'p3' union all
select 4, 2, 'p4' union all
select 5, 3, 'p5' union all
select 6, 3, 'p6' union all
select 7, 4, 'p7' union all
select 8, 5, 'p1' union all
select 9, 5, 'p2' union all
select 10,5, 'p83'
--select * from @.mockup

declare @.output table (PK int, pocketList varchar(200))

;with pocketList as
( select row_number () over
( partition by PK
order by pocket
) as seq,
row_number () over
( partition by PK
order by pocket desc
) as revSeq,
PK,
ID,
pocket
from @.mockup
), recursivePockets as
( select seq,
revSeq,
PK,
pocket,
cast(pocket as varchar(max)) as [product]
from pocketList
where revSeq = 1
union all
select b.seq,
b.revSeq,
b.PK,
b.pocket,
cast(b.pocket + ', ' + product as varchar(max))
from recursivePockets a
inner join pocketList b
on b.revSeq = a.revSeq + 1
and b.pk = a.pk
)

insert into @.output
select pk,
product
from recursivePockets
where seq = 1
order by pk

-- pk product
-- -- -
-- 1 p1, p2
-- 2 p3, p4
-- 3 p5, p6
-- 4 p7
-- 5 p1, p2, p83

( Ooops! Almost forgout to load it into an output table; sorry )

|||Have a read of FOR XML

Example

SELECT ' '+rtrim(ltrim(pocket))
from mytable
where pk = 2
order by pocket
FOR XML PATH('')

This will return you 1 row with a space in between each item you want to concat

Loop Question

Sorry if this is a bit of a newbie question.

I have three tables.

DECLARE @.TempLineStatusXref TABLE(
Run_Id int,
Line varchar(50),
master_unit_id int,
Start_Time datetime,
End_Time datetime,
Duration numeric(18,9),
Team varchar(50),
Excluded int
)

CREATE TABLE dbo.#TempVars(
Var_Id INTEGER,
Data_Type_Id INTEGER,
User_Defined1 VARCHAR(10),
PU_Order INTEGER,
PU_Id INTEGER,
RequiredinReport INTEGER)

DECLARE @.Required TABLE (
Var_id int,
Run_id int
)

In Table @.TempLineStatusXref table I have 8 records... 2 of them have a value of Excluded = 1... the other 6 have a value of Excluded = 0.

In Table #TempVars I have 334 values all of which are required.

What I want to do is for Each record in @.TempLineStatusXref that has a 0 in the Excluded table add the field Var_id from #TempVars and the Run_id from @.TempLineStatusXref.

So I should end up with (334 * 6) = 2004 records in @.Required

I appreciate any help.

Mark

Mark:

Try one of these two alternatives:

insert into @.required
select a.var_id,
b.run_id
from #tempVars a
inner join @.TempLineStatusXref b
on b.excluded = 0

or

insert into @.required
select a.var_id,
b.run_id
from #tempVars a
cross join @.TempLineStatusXref b
where b.excluded = 0

|||

yeap..

Insert into @.Required
Select Var_Id, Run_Id
From @.TempLineStatusXref,#TempVars
where Excluded = 0

it worked... thanks!!!!

looking for help to parse in t-sql

Hello,
I have a column of varchar that has data of 4-5 I need to manipulate the
data in a query to get it into another table to where the value would be
0040005. How could I accomplish this by parsing out the -? Other data could
be 12-99, 1-234 which in turn should be 0120099, 0010234. Any help would be
appreciated. Thanks.
JakeHope this helps
CREATE TABLE #test
(col1 VARCHAR(5))
INSERT INTO #test VALUES('4-5')
INSERT INTO #test VALUES('12-99')
INSERT INTO #test VALUES('1-234')
SELECT
CASE WHEN CHARINDEX('-', REVERSE(col1)) = 2 THEN RIGHT('0000000'
+REPLACE(col1,'-', '000'),7)
WHEN CHARINDEX('-', REVERSE(col1)) = 3 THEN RIGHT('0000000' +
REPLACE(col1,'-', '00'),7)
WHEN CHARINDEX('-', REVERSE(col1)) = 4 THEN RIGHT('0000000' +
REPLACE(col1,'-', '0'),7)
END
FROM #test
Thanks
GYK
"Jake" wrote:

> Hello,
> I have a column of varchar that has data of 4-5 I need to manipulate t
he
> data in a query to get it into another table to where the value would be
> 0040005. How could I accomplish this by parsing out the -? Other data coul
d
> be 12-99, 1-234 which in turn should be 0120099, 0010234. Any help would b
e
> appreciated. Thanks.
> Jake
>
>|||GYK,
This is great thank you. If possible could you walk me through this so I
could learn how it worked? I got the charindex but and confused with teh
reverse and then right +replace idea. I truly appreciate it.
Jake
"GYK" <GYK@.discussions.microsoft.com> wrote in message
news:6DDBD65E-2D05-4560-9E27-9E2CE9488774@.microsoft.com...[vbcol=seagreen]
> Hope this helps
> CREATE TABLE #test
> (col1 VARCHAR(5))
> INSERT INTO #test VALUES('4-5')
> INSERT INTO #test VALUES('12-99')
> INSERT INTO #test VALUES('1-234')
>
> SELECT
> CASE WHEN CHARINDEX('-', REVERSE(col1)) = 2 THEN RIGHT('0000000'
> +REPLACE(col1,'-', '000'),7)
> WHEN CHARINDEX('-', REVERSE(col1)) = 3 THEN RIGHT('0000000' +
> REPLACE(col1,'-', '00'),7)
> WHEN CHARINDEX('-', REVERSE(col1)) = 4 THEN RIGHT('0000000' +
> REPLACE(col1,'-', '0'),7)
> END
> FROM #test
> Thanks
> GYK
> "Jake" wrote:
>|||Let us consider one statement as an example:
WHEN CHARINDEX('-', REVERSE(col1)) = 2 THEN RIGHT('0000000'
+REPLACE(col1,'-', '000'),7)
--> First goal is to find out at what position '-' is available. So I used
charindex
--> But the position of '-' is not determined from left of the given
characters. For ex: charindex position is 2 for both 1st and 3rd row. So I
did the reverse of those characters. So that charindex is calculated from th
e
right of the char.
--> Replace function just replaces '-' with the specified characters . In
this case its '000'
--> Next step is to pad zeros to the left of the char. And you want to
retain the existing characters. So after the length of 40005, zero's will be
padded till the length becomes 7. See function RIGHT in BOL
Then I used case function to determine the position of '-' and the number of
zeros it should be replaced with.
If you generally look at these kind of statements, it looks complex but if
you breakdown into indivdual sections (from the inner params), it is pretty
easy.
HTH
GYK
"Jake" wrote:

> GYK,
> This is great thank you. If possible could you walk me through this so
I
> could learn how it worked? I got the charindex but and confused with teh
> reverse and then right +replace idea. I truly appreciate it.
> Jake
>
> "GYK" <GYK@.discussions.microsoft.com> wrote in message
> news:6DDBD65E-2D05-4560-9E27-9E2CE9488774@.microsoft.com...
>
>|||GYK
Thanks for the break down I appreciate it.
Jake
"GYK" <GYK@.discussions.microsoft.com> wrote in message
news:F960FC9A-7F2A-44D1-911D-4AB5E016CC9C@.microsoft.com...[vbcol=seagreen]
> Let us consider one statement as an example:
> WHEN CHARINDEX('-', REVERSE(col1)) = 2 THEN RIGHT('0000000'
> +REPLACE(col1,'-', '000'),7)
> --> First goal is to find out at what position '-' is available. So I used
> charindex
> --> But the position of '-' is not determined from left of the given
> characters. For ex: charindex position is 2 for both 1st and 3rd row. So I
> did the reverse of those characters. So that charindex is calculated from
> the
> right of the char.
> --> Replace function just replaces '-' with the specified characters . In
> this case its '000'
> --> Next step is to pad zeros to the left of the char. And you want to
> retain the existing characters. So after the length of 40005, zero's will
> be
> padded till the length becomes 7. See function RIGHT in BOL
> Then I used case function to determine the position of '-' and the number
> of
> zeros it should be replaced with.
> If you generally look at these kind of statements, it looks complex but if
> you breakdown into indivdual sections (from the inner params), it is
> pretty
> easy.
> HTH
> GYK
>
> "Jake" wrote:
>|||CREATE TABLE #test
(col1 VARCHAR(5))
INSERT INTO #test VALUES('4-5')
INSERT INTO #test VALUES('12-99')
INSERT INTO #test VALUES('1-234')
SELECT
RIGHT('000'+SUBSTRING(col1,1,CHARINDEX('
-',col1)-1),4)+RIGHT('0000'+SUBSTRIN
G(col1,CHARINDEX('-',col1)+1,10),5)
FROM #test
Gary
"Jake" <rondican@.hotmail.com> wrote in message
news:%23wbOrnlzEHA.2716@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have a column of varchar that has data of 4-5 I need to manipulate
the
> data in a query to get it into another table to where the value would be
> 0040005. How could I accomplish this by parsing out the -? Other data
could
> be 12-99, 1-234 which in turn should be 0120099, 0010234. Any help would
be
> appreciated. Thanks.
> Jake
>

looking for help to parse in t-sql

Hello,
I have a column of varchar that has data of 4-5 I need to manipulate the
data in a query to get it into another table to where the value would be
0040005. How could I accomplish this by parsing out the -? Other data could
be 12-99, 1-234 which in turn should be 0120099, 0010234. Any help would be
appreciated. Thanks.
JakeHope this helps
CREATE TABLE #test
(col1 VARCHAR(5))
INSERT INTO #test VALUES('4-5')
INSERT INTO #test VALUES('12-99')
INSERT INTO #test VALUES('1-234')
SELECT
CASE WHEN CHARINDEX('-', REVERSE(col1)) = 2 THEN RIGHT('0000000'
+REPLACE(col1,'-', '000'),7)
WHEN CHARINDEX('-', REVERSE(col1)) = 3 THEN RIGHT('0000000' +
REPLACE(col1,'-', '00'),7)
WHEN CHARINDEX('-', REVERSE(col1)) = 4 THEN RIGHT('0000000' +
REPLACE(col1,'-', '0'),7)
END
FROM #test
Thanks
GYK
"Jake" wrote:
> Hello,
> I have a column of varchar that has data of 4-5 I need to manipulate the
> data in a query to get it into another table to where the value would be
> 0040005. How could I accomplish this by parsing out the -? Other data could
> be 12-99, 1-234 which in turn should be 0120099, 0010234. Any help would be
> appreciated. Thanks.
> Jake
>
>|||GYK,
This is great thank you. If possible could you walk me through this so I
could learn how it worked? I got the charindex but and confused with teh
reverse and then right +replace idea. I truly appreciate it.
Jake
"GYK" <GYK@.discussions.microsoft.com> wrote in message
news:6DDBD65E-2D05-4560-9E27-9E2CE9488774@.microsoft.com...
> Hope this helps
> CREATE TABLE #test
> (col1 VARCHAR(5))
> INSERT INTO #test VALUES('4-5')
> INSERT INTO #test VALUES('12-99')
> INSERT INTO #test VALUES('1-234')
>
> SELECT
> CASE WHEN CHARINDEX('-', REVERSE(col1)) = 2 THEN RIGHT('0000000'
> +REPLACE(col1,'-', '000'),7)
> WHEN CHARINDEX('-', REVERSE(col1)) = 3 THEN RIGHT('0000000' +
> REPLACE(col1,'-', '00'),7)
> WHEN CHARINDEX('-', REVERSE(col1)) = 4 THEN RIGHT('0000000' +
> REPLACE(col1,'-', '0'),7)
> END
> FROM #test
> Thanks
> GYK
> "Jake" wrote:
>> Hello,
>> I have a column of varchar that has data of 4-5 I need to manipulate
>> the
>> data in a query to get it into another table to where the value would be
>> 0040005. How could I accomplish this by parsing out the -? Other data
>> could
>> be 12-99, 1-234 which in turn should be 0120099, 0010234. Any help would
>> be
>> appreciated. Thanks.
>> Jake
>>|||Let us consider one statement as an example:
WHEN CHARINDEX('-', REVERSE(col1)) = 2 THEN RIGHT('0000000'
+REPLACE(col1,'-', '000'),7)
--> First goal is to find out at what position '-' is available. So I used
charindex
--> But the position of '-' is not determined from left of the given
characters. For ex: charindex position is 2 for both 1st and 3rd row. So I
did the reverse of those characters. So that charindex is calculated from the
right of the char.
--> Replace function just replaces '-' with the specified characters . In
this case its '000'
--> Next step is to pad zeros to the left of the char. And you want to
retain the existing characters. So after the length of 40005, zero's will be
padded till the length becomes 7. See function RIGHT in BOL
Then I used case function to determine the position of '-' and the number of
zeros it should be replaced with.
If you generally look at these kind of statements, it looks complex but if
you breakdown into indivdual sections (from the inner params), it is pretty
easy.
HTH
GYK
"Jake" wrote:
> GYK,
> This is great thank you. If possible could you walk me through this so I
> could learn how it worked? I got the charindex but and confused with teh
> reverse and then right +replace idea. I truly appreciate it.
> Jake
>
> "GYK" <GYK@.discussions.microsoft.com> wrote in message
> news:6DDBD65E-2D05-4560-9E27-9E2CE9488774@.microsoft.com...
> > Hope this helps
> >
> > CREATE TABLE #test
> > (col1 VARCHAR(5))
> >
> > INSERT INTO #test VALUES('4-5')
> > INSERT INTO #test VALUES('12-99')
> > INSERT INTO #test VALUES('1-234')
> >
> >
> > SELECT
> > CASE WHEN CHARINDEX('-', REVERSE(col1)) = 2 THEN RIGHT('0000000'
> > +REPLACE(col1,'-', '000'),7)
> > WHEN CHARINDEX('-', REVERSE(col1)) = 3 THEN RIGHT('0000000' +
> > REPLACE(col1,'-', '00'),7)
> > WHEN CHARINDEX('-', REVERSE(col1)) = 4 THEN RIGHT('0000000' +
> > REPLACE(col1,'-', '0'),7)
> > END
> > FROM #test
> >
> > Thanks
> > GYK
> >
> > "Jake" wrote:
> >
> >> Hello,
> >>
> >> I have a column of varchar that has data of 4-5 I need to manipulate
> >> the
> >> data in a query to get it into another table to where the value would be
> >> 0040005. How could I accomplish this by parsing out the -? Other data
> >> could
> >> be 12-99, 1-234 which in turn should be 0120099, 0010234. Any help would
> >> be
> >> appreciated. Thanks.
> >>
> >> Jake
> >>
> >>
> >>
>
>|||GYK
Thanks for the break down I appreciate it.
Jake
"GYK" <GYK@.discussions.microsoft.com> wrote in message
news:F960FC9A-7F2A-44D1-911D-4AB5E016CC9C@.microsoft.com...
> Let us consider one statement as an example:
> WHEN CHARINDEX('-', REVERSE(col1)) = 2 THEN RIGHT('0000000'
> +REPLACE(col1,'-', '000'),7)
> --> First goal is to find out at what position '-' is available. So I used
> charindex
> --> But the position of '-' is not determined from left of the given
> characters. For ex: charindex position is 2 for both 1st and 3rd row. So I
> did the reverse of those characters. So that charindex is calculated from
> the
> right of the char.
> --> Replace function just replaces '-' with the specified characters . In
> this case its '000'
> --> Next step is to pad zeros to the left of the char. And you want to
> retain the existing characters. So after the length of 40005, zero's will
> be
> padded till the length becomes 7. See function RIGHT in BOL
> Then I used case function to determine the position of '-' and the number
> of
> zeros it should be replaced with.
> If you generally look at these kind of statements, it looks complex but if
> you breakdown into indivdual sections (from the inner params), it is
> pretty
> easy.
> HTH
> GYK
>
> "Jake" wrote:
>> GYK,
>> This is great thank you. If possible could you walk me through this
>> so I
>> could learn how it worked? I got the charindex but and confused with teh
>> reverse and then right +replace idea. I truly appreciate it.
>> Jake
>>
>> "GYK" <GYK@.discussions.microsoft.com> wrote in message
>> news:6DDBD65E-2D05-4560-9E27-9E2CE9488774@.microsoft.com...
>> > Hope this helps
>> >
>> > CREATE TABLE #test
>> > (col1 VARCHAR(5))
>> >
>> > INSERT INTO #test VALUES('4-5')
>> > INSERT INTO #test VALUES('12-99')
>> > INSERT INTO #test VALUES('1-234')
>> >
>> >
>> > SELECT
>> > CASE WHEN CHARINDEX('-', REVERSE(col1)) = 2 THEN RIGHT('0000000'
>> > +REPLACE(col1,'-', '000'),7)
>> > WHEN CHARINDEX('-', REVERSE(col1)) = 3 THEN RIGHT('0000000' +
>> > REPLACE(col1,'-', '00'),7)
>> > WHEN CHARINDEX('-', REVERSE(col1)) = 4 THEN RIGHT('0000000' +
>> > REPLACE(col1,'-', '0'),7)
>> > END
>> > FROM #test
>> >
>> > Thanks
>> > GYK
>> >
>> > "Jake" wrote:
>> >
>> >> Hello,
>> >>
>> >> I have a column of varchar that has data of 4-5 I need to
>> >> manipulate
>> >> the
>> >> data in a query to get it into another table to where the value would
>> >> be
>> >> 0040005. How could I accomplish this by parsing out the -? Other data
>> >> could
>> >> be 12-99, 1-234 which in turn should be 0120099, 0010234. Any help
>> >> would
>> >> be
>> >> appreciated. Thanks.
>> >>
>> >> Jake
>> >>
>> >>
>> >>
>>|||CREATE TABLE #test
(col1 VARCHAR(5))
INSERT INTO #test VALUES('4-5')
INSERT INTO #test VALUES('12-99')
INSERT INTO #test VALUES('1-234')
SELECT
RIGHT('000'+SUBSTRING(col1,1,CHARINDEX('-',col1)-1),4)+RIGHT('0000'+SUBSTRIN
G(col1,CHARINDEX('-',col1)+1,10),5)
FROM #test
Gary
"Jake" <rondican@.hotmail.com> wrote in message
news:%23wbOrnlzEHA.2716@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have a column of varchar that has data of 4-5 I need to manipulate
the
> data in a query to get it into another table to where the value would be
> 0040005. How could I accomplish this by parsing out the -? Other data
could
> be 12-99, 1-234 which in turn should be 0120099, 0010234. Any help would
be
> appreciated. Thanks.
> Jake
>

looking for help to parse in t-sql

Hello,
I have a column of varchar that has data of 4-5 I need to manipulate the
data in a query to get it into another table to where the value would be
0040005. How could I accomplish this by parsing out the -? Other data could
be 12-99, 1-234 which in turn should be 0120099, 0010234. Any help would be
appreciated. Thanks.
Jake
Hope this helps
CREATE TABLE #test
(col1 VARCHAR(5))
INSERT INTO #test VALUES('4-5')
INSERT INTO #test VALUES('12-99')
INSERT INTO #test VALUES('1-234')
SELECT
CASE WHEN CHARINDEX('-', REVERSE(col1)) = 2 THEN RIGHT('0000000'
+REPLACE(col1,'-', '000'),7)
WHEN CHARINDEX('-', REVERSE(col1)) = 3 THEN RIGHT('0000000' +
REPLACE(col1,'-', '00'),7)
WHEN CHARINDEX('-', REVERSE(col1)) = 4 THEN RIGHT('0000000' +
REPLACE(col1,'-', '0'),7)
END
FROM #test
Thanks
GYK
"Jake" wrote:

> Hello,
> I have a column of varchar that has data of 4-5 I need to manipulate the
> data in a query to get it into another table to where the value would be
> 0040005. How could I accomplish this by parsing out the -? Other data could
> be 12-99, 1-234 which in turn should be 0120099, 0010234. Any help would be
> appreciated. Thanks.
> Jake
>
>
|||GYK,
This is great thank you. If possible could you walk me through this so I
could learn how it worked? I got the charindex but and confused with teh
reverse and then right +replace idea. I truly appreciate it.
Jake
"GYK" <GYK@.discussions.microsoft.com> wrote in message
news:6DDBD65E-2D05-4560-9E27-9E2CE9488774@.microsoft.com...[vbcol=seagreen]
> Hope this helps
> CREATE TABLE #test
> (col1 VARCHAR(5))
> INSERT INTO #test VALUES('4-5')
> INSERT INTO #test VALUES('12-99')
> INSERT INTO #test VALUES('1-234')
>
> SELECT
> CASE WHEN CHARINDEX('-', REVERSE(col1)) = 2 THEN RIGHT('0000000'
> +REPLACE(col1,'-', '000'),7)
> WHEN CHARINDEX('-', REVERSE(col1)) = 3 THEN RIGHT('0000000' +
> REPLACE(col1,'-', '00'),7)
> WHEN CHARINDEX('-', REVERSE(col1)) = 4 THEN RIGHT('0000000' +
> REPLACE(col1,'-', '0'),7)
> END
> FROM #test
> Thanks
> GYK
> "Jake" wrote:
|||Let us consider one statement as an example:
WHEN CHARINDEX('-', REVERSE(col1)) = 2 THEN RIGHT('0000000'
+REPLACE(col1,'-', '000'),7)
--> First goal is to find out at what position '-' is available. So I used
charindex
--> But the position of '-' is not determined from left of the given
characters. For ex: charindex position is 2 for both 1st and 3rd row. So I
did the reverse of those characters. So that charindex is calculated from the
right of the char.
--> Replace function just replaces '-' with the specified characters . In
this case its '000'
--> Next step is to pad zeros to the left of the char. And you want to
retain the existing characters. So after the length of 40005, zero's will be
padded till the length becomes 7. See function RIGHT in BOL
Then I used case function to determine the position of '-' and the number of
zeros it should be replaced with.
If you generally look at these kind of statements, it looks complex but if
you breakdown into indivdual sections (from the inner params), it is pretty
easy.
HTH
GYK
"Jake" wrote:

> GYK,
> This is great thank you. If possible could you walk me through this so I
> could learn how it worked? I got the charindex but and confused with teh
> reverse and then right +replace idea. I truly appreciate it.
> Jake
>
> "GYK" <GYK@.discussions.microsoft.com> wrote in message
> news:6DDBD65E-2D05-4560-9E27-9E2CE9488774@.microsoft.com...
>
>
|||GYK
Thanks for the break down I appreciate it.
Jake
"GYK" <GYK@.discussions.microsoft.com> wrote in message
news:F960FC9A-7F2A-44D1-911D-4AB5E016CC9C@.microsoft.com...[vbcol=seagreen]
> Let us consider one statement as an example:
> WHEN CHARINDEX('-', REVERSE(col1)) = 2 THEN RIGHT('0000000'
> +REPLACE(col1,'-', '000'),7)
> --> First goal is to find out at what position '-' is available. So I used
> charindex
> --> But the position of '-' is not determined from left of the given
> characters. For ex: charindex position is 2 for both 1st and 3rd row. So I
> did the reverse of those characters. So that charindex is calculated from
> the
> right of the char.
> --> Replace function just replaces '-' with the specified characters . In
> this case its '000'
> --> Next step is to pad zeros to the left of the char. And you want to
> retain the existing characters. So after the length of 40005, zero's will
> be
> padded till the length becomes 7. See function RIGHT in BOL
> Then I used case function to determine the position of '-' and the number
> of
> zeros it should be replaced with.
> If you generally look at these kind of statements, it looks complex but if
> you breakdown into indivdual sections (from the inner params), it is
> pretty
> easy.
> HTH
> GYK
>
> "Jake" wrote:
|||CREATE TABLE #test
(col1 VARCHAR(5))
INSERT INTO #test VALUES('4-5')
INSERT INTO #test VALUES('12-99')
INSERT INTO #test VALUES('1-234')
SELECT
RIGHT('000'+SUBSTRING(col1,1,CHARINDEX('-',col1)-1),4)+RIGHT('0000'+SUBSTRIN
G(col1,CHARINDEX('-',col1)+1,10),5)
FROM #test
Gary
"Jake" <rondican@.hotmail.com> wrote in message
news:%23wbOrnlzEHA.2716@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have a column of varchar that has data of 4-5 I need to manipulate
the
> data in a query to get it into another table to where the value would be
> 0040005. How could I accomplish this by parsing out the -? Other data
could
> be 12-99, 1-234 which in turn should be 0120099, 0010234. Any help would
be
> appreciated. Thanks.
> Jake
>