Help me in Query

Question

Help me in Query

bala2

SSCertifiable

Points: 5547
More actions
June 28, 2012 at 1:07 am

#263330

We have a table strucure as below..
Tabe-A
A-d-int,pk,notnull
A_H--nvarchar(10),null
A_dn-navrchar
A-S-int,null
A_c-date(smalldatetime,null
Table-B,
B_d --int(PK,not null)
B_H---int,notnull,
B_Dn---nvarchar(14),notnull)
B_c_date--(smalldatetime,null)
B-si--(int,null)
B_DT--(smalldatetime,null)
when we are using the below insert into query we are got the error as "Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'D301510677' to data type int."
insert into B
(B_h, B_Dn, B_S, B_C_Date, B_dt)
(select A_H, A_DN, A_S, A_c_date, substring(A_DN, 3, 8)
from A
where A.A_dn + str(A.A_H) NOT in
(select B_DN + str(B_H) from B))
There is column datatype difference between columns B_H( int) and A_H(VAcrchar)
we need use the cast or convert ....WITH OUT CHANGING THE COLUMNS DATATYPES.
can any one help how to do that?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

rhythmk SSCertifiable Points: 7271 More actions · Answer 1

bala2 (6/28/2012)
We have a table strucure as below..
Tabe-A
A-d-int,pk,notnull
A_H--nvarchar(10),null
A_dn-navrchar
A-S-int,null
A_c-date(smalldatetime,null
Table-B,
B_d --int(PK,not null)
B_H---int,notnull,
B_Dn---nvarchar(14),notnull)
B_c_date--(smalldatetime,null)
B-si--(int,null)
B_DT--(smalldatetime,null)
when we are using the below insert into query we are got the error as "Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'D301510677' to data type int."
insert into B
(B_h, B_Dn, B_S, B_C_Date, B_dt)
(select A_H, A_DN, A_S, A_c_date, substring(A_DN, 3, 8)
from A
where A.A_dn + str(A.A_H) NOT in
(select B_DN + str(B_H) from DATA_ST_DESC))
There is column datatype difference between columns B_H( int) and A_H(VAcrchar)
we need use the cast or convert ....WITH OUT CHANGING THE COLUMNS DATATYPES.
can any one help how to do that?

you can not store character in int datatype.

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

The error message is fairly explicit. You've got a value in your nvarchar column that can't be converted to int because it has a "D" in it. The best thing to do is change the design of your database, if that's an option. If it's not, you either need to cleanse the data or find some way of dealing with values that can't be converted to int.

John

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 3

If you only want the numerical value of said column then you could drop the character like for example.

providing the alphabetical character is the first character you could do somehting like this

declare @t varchar(10),@t1 varchar(10)

set @t='d12345678'

set @t1='1234567'

select substring(@t,patindex('[a-z]',@t)+2,99),substring(@t1,patindex('[a-z]',@t),99)

***The first step is always the hardest *******

bala2 SSCertifiable Points: 5547 More actions · Answer 4

Tabe-A

A-d-int,pk,notnull

A_H--nvarchar(10),null

A_dn-navrchar

A-S-int,null

A_c-date(smalldatetime,null

Table-B,

B_d --int(PK,not null)

B_H---int,notnull,

B_Dn---nvarchar(14),notnull)

B_c_date--(smalldatetime,null)

B-si--(int,null)

B_DT--(smalldatetime,null)

when we are using the below insert into query we are got the error as "Msg 245, Level 16, State 1, Line 1

Conversion failed when converting the nvarchar value 'D301510677' to data type int."

insert into B

(B_h, B_Dn, B_S, B_C_Date, B_dt)

(select A_H, A_DN, A_S, A_c_date, substring(A_DN, 3, 8)

from A

where A.A_dn + str(A.A_H) NOT in

(select B_DN + str(B_H) from B))

I need to remove the Charater data "D" and need to inesert only Values in to the table(B).

Can any one help me in below query how to split the "D" or trucate it and How can i insert it into the table.

and SYantax which i have to add.

Here i need to trucate the A_H column from A table.

insert into B

(B_h, B_Dn, B_S, B_C_Date, B_dt)

(select A_H, A_DN, A_S, A_c_date, substring(A_DN, 3, 8)

from A

where A.A_dn + str(A.A_H) NOT in

(select B_DN + str(B_H) from B))

Please guide me .....and

Many THanks.

rhythmk SSCertifiable Points: 7271 More actions · Answer 5

bala2 (7/9/2012)
Tabe-A
A-d-int,pk,notnull
A_H--nvarchar(10),null
A_dn-navrchar
A-S-int,null
A_c-date(smalldatetime,null
Table-B,
B_d --int(PK,not null)
B_H---int,notnull,
B_Dn---nvarchar(14),notnull)
B_c_date--(smalldatetime,null)
B-si--(int,null)
B_DT--(smalldatetime,null)
when we are using the below insert into query we are got the error as "Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'D301510677' to data type int."
insert into B
(B_h, B_Dn, B_S, B_C_Date, B_dt)
(select A_H, A_DN, A_S, A_c_date, substring(A_DN, 3, 8)
from A
where A.A_dn + str(A.A_H) NOT in
(select B_DN + str(B_H) from B))
I need to remove the Charater data "D" and need to inesert only Values in to the table(B).
Can any one help me in below query how to split the "D" or trucate it and How can i insert it into the table.
and SYantax which i have to add.
Here i need to trucate the A_H column from A table.
insert into B
(B_h, B_Dn, B_S, B_C_Date, B_dt)
(select A_H, A_DN, A_S, A_c_date, substring(A_DN, 3, 8)
from A
where A.A_dn + str(A.A_H) NOT in
(select B_DN + str(B_H) from B))
Please guide me .....and
Many THanks.

Have a look on above post by SGT_squeequal.

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

Lowell SSC Guru Points: 323518 More actions · Answer 6

if you search the forums here, there is a function "dbo.StripnonNumeric" that could help as well;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

bala2 SSCertifiable Points: 5547 More actions · Answer 7

Hi,

Many thanks for reply,

I have used this command

select REPLACE(LTRIM(REPLACE(A_H, 'D', ' ')), ' ', ' ') from tableA

It is working .But is displaying space before the numric.

IS ther any way to remove this space before.

Lowell SSC Guru Points: 323518 More actions · Answer 8

you replaced "D" with a space.

repalce with an empty string instead:

select LTRIM(REPLACE(A_H, 'D', '')) from tableA

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

rhythmk SSCertifiable Points: 7271 More actions · Answer 9

bala2 (7/9/2012)
Hi,
Many thanks for reply,
I have used this command
select REPLACE(LTRIM(REPLACE(A_H, 'D', ' ')), ' ', ' ') from tableA
It is working .But is displaying space before the numric.
IS ther any way to remove this space before.

Remove external REPLACE.

But do you have the set pattern i.e. every column value is having prefix as 'D' ?

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂