Help with sql

Hi,
This might be a simple one .. I have a table with two fields (There are more
but Ill concentrate on this ones).
One field is 'address', the other is 'address2', both are nvarchar
If I want to select both into another table but 'concatenate them' how can I
do this ?
At the moment this is what I have
Select address, address2
into table2
from table1
How can I have something like
Select address + address2 as BIGaddress
into table2
from table1
AleksYour statement seems to be ok...or I haven't understood your question...
Select address + address2 as BIGaddress
into table2
from table1
Francesco Anti
"Aleks" <arkark2004@.hotmail.com> wrote in message
news:%23vZHbeDbFHA.3328@.TK2MSFTNGP10.phx.gbl...
> Hi,
> This might be a simple one .. I have a table with two fields (There are
> more but Ill concentrate on this ones).
> One field is 'address', the other is 'address2', both are nvarchar
> If I want to select both into another table but 'concatenate them' how can
> I do this ?
> At the moment this is what I have
> Select address, address2
> into table2
> from table1
> How can I have something like
> Select address + address2 as BIGaddress
> into table2
> from table1
> Aleks
>|||What you have should work fine
Select address + address2 as BIGaddress
into table2
from table1
The SELECT INTO will create a new table called table2 with one column called
BIGaddress
If table2 already exists then change the SELECT INTO into an INSERT
"Aleks" <arkark2004@.hotmail.com> wrote in message
news:#vZHbeDbFHA.3328@.TK2MSFTNGP10.phx.gbl...
> Hi,
> This might be a simple one .. I have a table with two fields (There are
more
> but Ill concentrate on this ones).
> One field is 'address', the other is 'address2', both are nvarchar
> If I want to select both into another table but 'concatenate them' how can
I
> do this ?
> At the moment this is what I have
> Select address, address2
> into table2
> from table1
> How can I have something like
> Select address + address2 as BIGaddress
> into table2
> from table1
> Aleks
>|||The query which you have given will work Right ?
Regards,
Peri
"Aleks" <arkark2004@.hotmail.com> wrote in message
news:#vZHbeDbFHA.3328@.TK2MSFTNGP10.phx.gbl...
> Hi,
> This might be a simple one .. I have a table with two fields (There are
more
> but Ill concentrate on this ones).
> One field is 'address', the other is 'address2', both are nvarchar
> If I want to select both into another table but 'concatenate them' how can
I
> do this ?
> At the moment this is what I have
> Select address, address2
> into table2
> from table1
> How can I have something like
> Select address + address2 as BIGaddress
> into table2
> from table1
> Aleks
>|||Just watch out for two things:
1. If either address or address2 is NULL, the concatenation will be NULL.
You can use this instead: coalesce(address,N'') + coalesce(address2,N'')
2. If address+address2 exceeds 4000 characters, you will lose information.
Steve Kass
Drew University
Aleks wrote:

>Hi,
>This might be a simple one .. I have a table with two fields (There are mor
e
>but Ill concentrate on this ones).
>One field is 'address', the other is 'address2', both are nvarchar
>If I want to select both into another table but 'concatenate them' how can
I
>do this ?
>At the moment this is what I have
>Select address, address2
>into table2
>from table1
>How can I have something like
>Select address + address2 as BIGaddress
>into table2
>from table1
>Aleks
>
>|||Jaja .. I didnt even tested and worked fine ... how silly
A
"Steve Kass" <skass@.drew.edu> wrote in message
news:u27rGxDbFHA.2440@.TK2MSFTNGP10.phx.gbl...
> Just watch out for two things:
> 1. If either address or address2 is NULL, the concatenation will be NULL.
> You can use this instead: coalesce(address,N'') + coalesce(address2,N'')
> 2. If address+address2 exceeds 4000 characters, you will lose information.
> Steve Kass
> Drew University
>
> Aleks wrote:
>

Help with Select to return Duplicate rows

Can someone look at this and tell me where I went wrong? I'm trying to return all duplicate rows that have the same lastName and Address. It returns rows but they don't look like dups.

SELECT TOP (100)PERCENT dbo.tblClient.LastName, dbo.tblClientAddresses.AddressFROM dbo.tblClientINNERJOIN dbo.tblClientAddressesON dbo.tblClient.Client_ID = dbo.tblClientAddresses.Client_IDGROUP BY dbo.tblClient.LastName, dbo.tblClientAddresses.AddressHAVING (COUNT(dbo.tblClientAddresses.Address) > 1)ORDER BY dbo.tblClientAddresses.Address

TOP 100 PERCENT is redundant.

SELECT dbo.tblClient.LastName, dbo.tblClientAddresses.Address,count(*)FROM dbo.tblClientINNERJOIN dbo.tblClientAddressesON dbo.tblClient.Client_ID = dbo.tblClientAddresses.Client_IDGROUP BY dbo.tblClient.LastName, dbo.tblClientAddresses.AddressHAVINGCOUNT(*) > 1ORDER BY dbo.tblClientAddresses.Address

|||

ndinakar,

Is there a way to also return the Client_ID of each row?

|||

Hi Jack,

If the Client_ID is a primary key with unique values, so the duplicate rows have different Client_ID, we cannot return it.

If duplicate rows have the same Client_ID, yes, we can return it.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

|||

try dinakar's query just add client id in select list. but dont group by client id. i think that should work.

thanks,

satish.

Help with math in function

I have a function that reads an unsigned integer and translates it into an IP address. The problem is that it is rounding the numbers so the IP numbers are off by 1 either up or down, i.e., 144.xxx.xxx.xx will show as 145.xxx.... or 144.456.xx... will show as 144.455.xx....Can someone help?

Here is the code:

create or replace function readip(unsint number)
return varchar
is
ip_address varchar(15);
a number(3);
b number(3);
c number(3);
d number(3);

begin
a := mod( ( unsint / 16777216 ), 256);
b := mod( ( unsint / 65536 ), 256);
c := mod( ( unsint / 256 ), 256);
d := mod( ( unsint ), 256);

ip_address := a||'.'||b||'.'||c||'.'||d;

return ip_address;
end readip;

Thank you much!

MonicaIf you want the decimals to be displayed, define the variables as
a number(10,3);

Hope this helps.|||Originally posted by DBW-Monica
I have a function that reads an unsigned integer and translates it into an IP address. The problem is that it is rounding the numbers so the IP numbers are off by 1 either up or down, i.e., 144.xxx.xxx.xx will show as 145.xxx.... or 144.456.xx... will show as 144.455.xx....Can someone help?

Here is the code:

create or replace function readip(unsint number)
return varchar
is
ip_address varchar(15);
a number(3);
b number(3);
c number(3);
d number(3);

begin
a := mod( ( unsint / 16777216 ), 256);
b := mod( ( unsint / 65536 ), 256);
c := mod( ( unsint / 256 ), 256);
d := mod( ( unsint ), 256);

ip_address := a||'.'||b||'.'||c||'.'||d;

return ip_address;
end readip;

Thank you much!

Monica
must be missing the obvious, but I just don't get it. Can you give an example of an input value, and what your function should return ?