Converting data question

I need to combine two columns and copy them to another. One column is varchar and one is decimal (3,0) and the destination column is int. The column of decimal contains only 1 and 2 digit numbers. I need to perface a 0 to the data with 1 digit before combining the two columns. If someone could guide me here, I'd appreciate it. I've been trying to use cast and convert with no success.Your destination column should be a varchar column if the column from your source varchar is not an integer. You can post a set of sample data and expected result here to help answer your question.|||Source Columns Destination
Varchar Dec int
0660 10 66010
0660 2 66002
0660 3 66003
0660 11 66011|||

Kinny:

I put together a quick mock-up of what I think you are trying to do. Do either of the output columns look like what you are trying to do?

Dave

-- --
-- This mock-up works correctly for "correct values" as discribed in the request.
-- However, it does not know what to do with most "error conditions." If you don't
-- have to worry about "incorrect values" this select should work fine
--
-- Problem areas:
--
-- 1. What do you do with nulls?
-- 2. What do you do with 3-digit numbers?
-- 3. What do you do with negative numbers?
-- --
set nocount on
declare @.mockUp table
( sampleString varchar (20) not null,
sampleDecimal decimal (3,0) null
)

insert into @.mockUp values ('testView', null) -- ? Do you have to worry about null?
-- ? If so, display double-zero for nulls?
insert into @.mockUp values ('testView', 0) -- O0; OK
insert into @.mockUp values ('testView', 4) -- 04; OK
insert into @.mockUp values ('testView', 21) -- 21; OK
insert into @.mockUp values ('nextSample', 99) -- 99; OK
insert into @.mockUp values ('negative', -1) -- -1; Not sure; is this a problem?
insert into @.mockUp values ('negative', -99) -- 99; Wrong; is this a problem?
insert into @.mockUp values ('tooBig', 100) -- 00; Wrong; is this a problem?
--select * from @.mockUp

select sampleString,
sampleDecimal,
sampleString + isnull ( right('0'+convert(varchar(3), sampleDecimal), 2), '00')
as [String First],
isnull ( right('0'+convert(varchar(3), sampleDecimal), 2), '00') + sampleString
as [Decimal First]
from @.mockUp

-- --
-- Sample Output:
-- --

-- sampleString sampleDecimal String First Decimal First
-- -- - - -
-- testView NULL testView00 00testView
-- testView 0 testView00 00testView
-- testView 4 testView04 04testView
-- testView 21 testView21 21testView
-- nextSample 99 nextSample99 99nextSample
-- negative -1 negative-1 -1negative
-- negative -99 negative99 99negative
-- tooBig 100 tooBig00 00tooBig

|||

Hi,

Here is the update statement for question:

UPDATE yourTable

SET DesINTColumn= CAST(CAST(CONVERT(int, VarCharColumn1) AS varchar)+ RIGHT('0' + CAST(DecimalColumn1 AS varchar), 2) AS INT)

converting a column from varchar to int

I need to copy the data from varchar column into an integer column,
obviously only copying integer values. How can I do this? I am not even
sure how to select data from a column so that the results are only the
numeric ones. Thanks for your help.Et,
ALTER TABLE should allow you to change the datatype of the table assuming
all values are integers.
You could run something like this:
UPDATE tablename
SET col2 = CAST(col1 AS int) --would require the values to be integers not
characters
Check out the ISNUMERIC function for differentiating between integers and
characters. Also see:
http://www.aspfaq.com/show.asp?id=2390
HTH
Jerry
"et" <eagletender2001@.yahoo.com> wrote in message
news:%23nc3kkdxFHA.1252@.TK2MSFTNGP09.phx.gbl...
>I need to copy the data from varchar column into an integer column,
>obviously only copying integer values. How can I do this? I am not even
>sure how to select data from a column so that the results are only the
>numeric ones. Thanks for your help.
>|||Yes, the isnumeric function worked, the cast of course gave me an error
because not all values are integers. This is what I did:
update tbl set newcol=originalcol
where isnumeric(originalcol)=1
Now I have to populate the remaining fields with an integer, any idea an
easy way to do that? I'd like to do it starting with the next available
number, but it doesn't really matter what the number ends up being. This
column will eventually be an identity column.
Thanks!
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eMYJevdxFHA.2232@.TK2MSFTNGP11.phx.gbl...
> Et,
> ALTER TABLE should allow you to change the datatype of the table assuming
> all values are integers.
> You could run something like this:
> UPDATE tablename
> SET col2 = CAST(col1 AS int) --would require the values to be integers
> not characters
> Check out the ISNUMERIC function for differentiating between integers and
> characters. Also see:
> http://www.aspfaq.com/show.asp?id=2390
> HTH
> Jerry
>
> "et" <eagletender2001@.yahoo.com> wrote in message
> news:%23nc3kkdxFHA.1252@.TK2MSFTNGP09.phx.gbl...
>|||ok...clarify please...IDENTITY is system generated so you don't populate it
with any values. "any number"?
"et" <eagletender2001@.yahoo.com> wrote in message
news:O3CsMRhxFHA.464@.TK2MSFTNGP15.phx.gbl...
> Yes, the isnumeric function worked, the cast of course gave me an error
> because not all values are integers. This is what I did:
> update tbl set newcol=originalcol
> where isnumeric(originalcol)=1
> Now I have to populate the remaining fields with an integer, any idea an
> easy way to do that? I'd like to do it starting with the next available
> number, but it doesn't really matter what the number ends up being. This
> column will eventually be an identity column.
> Thanks!
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:eMYJevdxFHA.2232@.TK2MSFTNGP11.phx.gbl...
>|||That's correct. In other words if this database had been done correctly in
the first place, it would have been an identity column. So now I need to
create this identity column based on numbers that already exist, plus
populate those records with new numbers for those records that don't use an
integer value. Once this is done, then I can make the column an identity
column so that future new records are automatically numbered correctly.
Does that make sense?
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eBZ%23xThxFHA.1412@.TK2MSFTNGP09.phx.gbl...
> ok...clarify please...IDENTITY is system generated so you don't populate
> it with any values. "any number"?
> "et" <eagletender2001@.yahoo.com> wrote in message
> news:O3CsMRhxFHA.464@.TK2MSFTNGP15.phx.gbl...
>|||You cannot alter an existing column to be an identity column. You'll have to
create a new table with the identity column and then copy the rows from the
old table into the new one using SET IDENTITY_INSERT ON.
Why are you doing this? Are you planning on using the identity column as the
primary key? Analyze your data more thoroughly - there may be a better
primary key candidate.
To clean up those values try this:
http://milambda.blogspot.com/2005/0...to-integer.html
ML|||No, my primary key is a guid. This column used to be the primary key in an
access database, and was also used for other reasons. Now it's being merged
with the company's sql database. I'm only using the identity feature so it
will automatically provide a number each time a new record is created, which
of course I can do within the program it's being used with also.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:53200E31-72FB-4C15-AF6B-CF6858E3296E@.microsoft.com...
> You cannot alter an existing column to be an identity column. You'll have
> to
> create a new table with the identity column and then copy the rows from
> the
> old table into the new one using SET IDENTITY_INSERT ON.
> Why are you doing this? Are you planning on using the identity column as
> the
> primary key? Analyze your data more thoroughly - there may be a better
> primary key candidate.
> To clean up those values try this:
> http://milambda.blogspot.com/2005/0...to-integer.html
>
> ML|||If the values still need to be unique, then I'd suggest letting the server
assign them.
ML

convert nvarchar to int

i have got a table

id name pagenumber(nvarchar)

1 gas pg:231-123

2 dff pg:323-123

i need to copy data from this table to another with page number as

id name pagenumber(int)

1 gas 231

2 dff 323

help me

here it is,

Create Table #data (

[id] Varchar(100) ,

[name] Varchar(100) ,

[pagenumber] Varchar(100)

);

Insert Into #data Values( '1','gas','pg:231-123');

Insert Into #data Values( '2','dff','pg:323-123');

select id,name,substring(pagenumber,charindex(':',pagenumber) +1, charindex('-',pagenumber)-charindex(':',pagenumber)-1)

From

#data

|||

Question to 'nasrene',

Are ALL PageNumber values in the form of 'pg:n%-n%'

No space after the colon, alway a dash separating two sets of characters, no other variations?|||

yes all values n form pg.n%-n%

eg:

pg.23-123.

pg.1-23.

pg. 2-34.

for some there are spacesafter pg.

|||

gets error

Invalid length parameter passed to the substring function.
values eg:

pg.23-123.

pg.2-123.

pg. 2-12.

|||

It now appears that the character after 'pg' is a period. Previously it was a colon.

If it is a period, replace the colon in Manivannan's with a period.

select id,name,substring(pagenumber,charindex(':',pagenumber) +1, charindex('-',pagenumber)-charindex(':',pagenumber)-1)

From

#data

[/quote/

|||i replaced but same error|||

Contrary to your earlier assertion, some of your data does NOT follow the pattern [ pg.n%-n% ].

The error is due to one or more rows NOT containing a dash [-].

Try this query to find the rows that do not have a dash.

SELECT *

FROM MyTable

WHERE MyColumn NOT LIKE '%-%'

|||

THERE are three rows that dont contain a '-'

so what to do

|||

This variation 'should' handle your situation.

All that is required is that the Page Number contain the characters 'pg'. It doesn't matter if there is a colon, a period, a dash, or only the first group of numbers.

Code Snippet

DECLARE @.Data table
( [ID] varchar(20) ,
[Name] varchar(20) ,
[PageNumber] varchar(20)
);

INSERT INTO @.Data VALUES ( '1', 'Gas', 'pg.231-123' );
INSERT INTO @.Data VALUES ( '2', 'Oil', 'pg.323-123' );
INSERT INTO @.Data VALUES ( '3', 'Benzene', 'pg.323' );
INSERT INTO @.Data VALUES ( '4', 'Oxygen', 'pg 323-123' );
INSERT INTO @.Data VALUES ( '5', 'Lubricant', 'pg:323-123' );
INSERT INTO @.Data VALUES ( '6', 'Friction', 'pg. 23' );

SELECT
ID,
Name,
Page = ltrim(substring(PageNumber,(charindex('pg',PageNumber)+3), (isnull(len(PageNumber)-(charindex('pg',PageNumber)+2),0))))
FROM @.Data;

ID Name Page
-- -- --
1 Gas 231-123
2 Oil 323-123
3 Benzene 323
4 Oxygen 323-123
5 Lubricant 323-123
6 Friction 23

|||

need the answer as

id name page

1 gas 231

2 oil 323

etc

|||

This should correct that:

DECLARE @.Data table
( [ID] varchar(20) ,
[Name] varchar(20) ,
[PageNumber] varchar(20)
);

INSERT INTO @.Data VALUES ( '1', 'Gas', 'pg.231-123' );
INSERT INTO @.Data VALUES ( '2', 'Oil', 'pg.323-123' );
INSERT INTO @.Data VALUES ( '3', 'Benzene', 'pg.323' );
INSERT INTO @.Data VALUES ( '4', 'Oxygen', 'pg 323-123' );
INSERT INTO @.Data VALUES ( '5', 'Lubricant', 'pg:323-123' );
INSERT INTO @.Data VALUES ( '6', 'Friction', 'pg. 23' );

SELECT
ID,
Name,
Page = ltrim(substring(PageNumber,(charindex('pg',PageNumber)+3), (isnull(len(PageNumber)-(charindex('-',PageNumber)),0))))

FROM @.Data;

/*

ID Name Page
-- -- --
1 Gas 231
2 Oil 323
3 Benzene 323
4 Oxygen 323
5 Lubricant 323
6 Friction 23

*/

|||

I DONT GET THE CORRECT ANSWER

THE ANSWER I GOT WAS 231-,323- ETC

NEED THE ANSWER AS 231,323

|||

The code supplied immediately above provides the data in the form you requested -UNLESS there is something about the data that you have not clearly communicated. (Carefully examine the sample data included to find out if there is something different about how your data is stored.)

I suggest that you copy the code above to a new query window, and execute it. You 'should' then see that the resulset is as you requested. Then you may substitute your table/column names and 'tweak' it until it works for you.

|||

pp.79-103.
pp.63-78.
pp.45-62.
pp.229-254.
pp.147-164.
pp.75-81.
pp.35-56.
pp.23-33.
pp.5-21.
pp.5-13.
pp.268-282.
pp.257-267.