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.