Case Statement

Hi!

I need a case that returns the result of a select if it is not null, and -1 if it is null. I did it this way:

select

case

when(select column from table where conditions) is null then -1

else(select column from table where conditions)

But it doesn't seem very clever to repeat the select statement. Is there any way I can do it without repeating the "select column from table where conditions"?

Thank you!

Try this:

select column = case column
when null then -1
else column
end
from table
where conditions

|||

You can write it like below which is ANSI SQL syntax:

select coalesce(column , -1) as column from table

COALESCE is just a short-hand for a special form of CASE expression like:

case when expr1 is not null then expr1

when expr2 is not null then expr2

...

end

Another proprietary TSQL method is to use isnull function:

select isnull(column, -1) as column from table

|||

Try either of

select coalesce(col, -1) from tab
go

select isnull(col, -1) from tab
go

Unfortunately, Allen's suggestion doesn't work because the "null" appearing in the when_expression causes it to always evaluate to false.

|||

Now it seems clever! :)

Thank you!!!

|||

Allen was almost there.

If you change this example slightly:

select column = case column
when null then -1
else column
end
from table
where conditions

to this..

select column = case
when column is null then -1
else column
end
from table
where conditions

..it'll work as expected

=;o)
/Kenneth

Case Sensitive Object Names?

My new DB seems to be "case-sensitive". I have a table named Bld_List.

Select * From Bld_List

returns all records in QA. However, if I don't follow the correct case

Select * From bld_list

I get this error: Invalid object name 'bld_list'.

Also, when I try to get rid of some unnecessary records by using this

DELETE
FROM Bld_List
WHERE (PRODUCT IN
(SELECT DISTINCT Product
FROM BldOff_Inv_Daily))

I get this error: Cannot resolve collation conflict for equal to operation.

I imagine I set something up wrong when I first built the DB, but I don't know what to look for.

ThanksI'd suspect you are not using the default character set collations. Character sets can be either case-sensitive or case-insensitive, and I think this applies not just to the data but to the object names as well.|||So is that something I can change?

I used the Collation Name that my other databases are set to:
SQL_Latin1_General_Cp1_CI_AS
or is it some other setting?|||Hi,

CI is pointing that the collation is Case Insensitive.

If you take a create script of your tables, you can see if a non-default collation is used.

Eralper
http://www.kodyaz.com|||What does sp_helpsort tell you?|||Hi,

CI is pointing that the collation is Case Insensitive.

If you take a create script of your tables, you can see if a non-default collation is used.

Eralper
http://www.kodyaz.com

hmmm...
[PRODUCT] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL

Do I need to rebuild and repopulate the table? I only have a few so far, so it wouldn't be a huge deal.|||What does sp_helpsort tell you?

Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data|||Ok, I think I've got it. I did change the collation at some point over the weekend. Any tables I created after that are okay.
If I just use the Alter Table Collation clause on the first tables I created, that should take care of the problem, right?

CASE returning different data types

Hi, folks...
I was trying to write a sql query which returns different data types, but it
returns every time, a smalldatetime format...
SELECT CASE WHEN Tipo = 'N' THEN Numerico
WHEN Tipo = 'L' THEN Logico
WHEN Tipo = 'P' THEN Percentual
WHEN Tipo = 'D' THEN Data
ELSE NULL
END as ValorParametro
FROM ParametrosEmissores
WHERE Codigo = 'TRUNCANOME'
'Numerico' is an integer field
'L' is a bit field
'P' is a float field
'D' is a smalldatetime field.
Whatever is the datatype returning, the result is a smalldatetime field...
Any help would be apreciated.
Daniela.CASE is an expression -- and by definition every path of an expression must
return the same datatype (think of a function in a procedural language --
any function you define can only have a single return datatype). You might
consider, in this case, casting all return values to a string datatype.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Daniela Binatti" <Daniela Binatti@.discussions.microsoft.com> wrote in
message news:39E5A440-51BB-4DAF-A73B-8BDFD9E136F7@.microsoft.com...
> Hi, folks...
> I was trying to write a sql query which returns different data types, but
it
> returns every time, a smalldatetime format...
> SELECT CASE WHEN Tipo = 'N' THEN Numerico
> WHEN Tipo = 'L' THEN Logico
> WHEN Tipo = 'P' THEN Percentual
> WHEN Tipo = 'D' THEN Data
> ELSE NULL
> END as ValorParametro
> FROM ParametrosEmissores
> WHERE Codigo = 'TRUNCANOME'
> 'Numerico' is an integer field
> 'L' is a bit field
> 'P' is a float field
> 'D' is a smalldatetime field.
> Whatever is the datatype returning, the result is a smalldatetime field...
> Any help would be apreciated.
> Daniela.
>|||No, can't do that.. ALL Possible data values that could come out of a case
must be the same datatype. That's because the datatype is not associated
with the data item itself, but with the COLUMN of the resulting Resultset.
SQL has t oassigna a datatypes to the Column...
But not all s lost... You simply have t oCAST The data in each primitive
Table colum,n to the same datatype in the case statement
SELECT CASE WHEN Tipo = 'N' THEN Cast(Numerico As VarChar(20))
WHEN Tipo = 'L' And Logico = 1 THEN 'True'
WHEN Tipo = 'L' And Logico = 0 THEN 'False'
WHEN Tipo = 'P' THEN Cast(Percentual As VarChar(20))
WHEN Tipo = 'D' THEN Convert(VarChar(20), Data, 112)
ELSE NULL
END as ValorParametro
FROM ParametrosEmissores
WHERE Codigo = 'TRUNCANOME'
"Daniela Binatti" wrote:

> Hi, folks...
> I was trying to write a sql query which returns different data types, but
it
> returns every time, a smalldatetime format...
> SELECT CASE WHEN Tipo = 'N' THEN Numerico
> WHEN Tipo = 'L' THEN Logico
> WHEN Tipo = 'P' THEN Percentual
> WHEN Tipo = 'D' THEN Data
> ELSE NULL
> END as ValorParametro
> FROM ParametrosEmissores
> WHERE Codigo = 'TRUNCANOME'
> 'Numerico' is an integer field
> 'L' is a bit field
> 'P' is a float field
> 'D' is a smalldatetime field.
> Whatever is the datatype returning, the result is a smalldatetime field...
> Any help would be apreciated.
> Daniela.
>|||If they are different types then how do you want to display them in a
single column? Maybe we could help you better if you explain more about
what you are trying to do.
David Portas
SQL Server MVP
--|||Thank you very much. I've sorted out the problem by converting every result
in a sql_variant field.
"Daniela Binatti" wrote:

> Hi, folks...
> I was trying to write a sql query which returns different data types, but
it
> returns every time, a smalldatetime format...
> SELECT CASE WHEN Tipo = 'N' THEN Numerico
> WHEN Tipo = 'L' THEN Logico
> WHEN Tipo = 'P' THEN Percentual
> WHEN Tipo = 'D' THEN Data
> ELSE NULL
> END as ValorParametro
> FROM ParametrosEmissores
> WHERE Codigo = 'TRUNCANOME'
> 'Numerico' is an integer field
> 'L' is a bit field
> 'P' is a float field
> 'D' is a smalldatetime field.
> Whatever is the datatype returning, the result is a smalldatetime field...
> Any help would be apreciated.
> Daniela.
>

Cascading address returns in query

I have a table with users. These users are joined to an address table
by an intermediary table called user_address, which maps the
addressids to the userids. A user can have multiple addresses,
denoted in the Address table by an int identifying the type of
address.
Here's my issue...
I need to return a list of users with their addresses. but... I need
only to see the user's name once. If they have an address type = 1,
then i need that returned. If they don't have an addresstype 1, then
I need the address for the addresstype 2, etc. for four types of
addresses (head office, physical, billing, other).
Here's what the tables look like:
User_Address
UserIDint
AddressIDint
Address
AddressIDint
Address1varchar(100)
Address2varchar(100)
Address3varchar(100)
Address4varchar(100)
Citynvarchar(100)
PostalCodevarchar(10)
Countrysmallint
AddressTypeint
CreateDatesmalldatetime
CreateUserIDint
Updatedatesmalldatetime
UpdateUserIDint
ProvinceTypeint
Inactivebit
The Users table has the standard user info, as well as a companyid. I
basically need all the contacts for a company, with the cascading
if... else looping statement for the address.
I haven't been able to find anything online about this... any help is
greatly appreciated.
Stacy
On Feb 28, 8:22 pm, "CalgaryDataGrl" <calgarydata...@.gmail.com> wrote:
> I have a table with users. These users are joined to an address table
> by an intermediary table called user_address, which maps the
> addressids to the userids. A user can have multiple addresses,
> denoted in the Address table by an int identifying the type of
> address.
> Here's my issue...
> I need to return a list of users with their addresses. but... I need
> only to see the user's name once. If they have an address type = 1,
> then i need that returned. If they don't have an addresstype 1, then
> I need the address for the addresstype 2, etc. for four types of
> addresses (head office, physical, billing, other).
> Here's what the tables look like:
> User_Address
> UserID int
> AddressID int
> Address
> AddressID int
> Address1 varchar(100)
> Address2 varchar(100)
> Address3 varchar(100)
> Address4 varchar(100)
> City nvarchar(100)
> PostalCode varchar(10)
> Country smallint
> AddressType int
> CreateDate smalldatetime
> CreateUserID int
> Updatedate smalldatetime
> UpdateUserID int
> ProvinceType int
> Inactive bit
> The Users table has the standard user info, as well as a companyid. I
> basically need all the contacts for a company, with the cascading
> if... else looping statement for the address.
> I haven't been able to find anything online about this... any help is
> greatly appreciated.
> Stacy
Can you see if this works for you?
Declare @.User table(UserID int, UserName varchar(50))
Declare @.UserAddress table(UserID int, AddressType int, Address
varchar(50))
insert into @.User values(1,'Emp1')
insert into @.User values(2,'Emp2')
insert into @.User values(3,'Emp3')
insert into @.User values(4,'Emp4')
insert into @.User values(5,'Emp5')
insert into @.UserAddress values(1,1,'Addr11')
insert into @.UserAddress values(1,2,'Addr12')
insert into @.UserAddress values(1,3,'Addr13')
insert into @.UserAddress values(1,4,'Addr14')
insert into @.UserAddress values(2,1,'Addr21')
insert into @.UserAddress values(2,2,'Addr22')
insert into @.UserAddress values(2,3,'Addr23')
insert into @.UserAddress values(3,2,'Addr32')
insert into @.UserAddress values(3,3,'Addr33')
insert into @.UserAddress values(4,1,'Addr41')
insert into @.UserAddress values(4,3,'Addr43')
insert into @.UserAddress values(4,4,'Addr44')
insert into @.UserAddress values(5,4,'Addr54')
Select T.UserID, T.AddressType, U.UserName, A.Address from
(Select Distinct(A.UserID) as UserID, min(A.AddressType)as AddressType
from @.UserAddress A
Group by A.UserID) T
Inner Join @.User U ON U.UserID=T.UserID
INNER JOIN @.UserAddress A ON (A.AddressType = T.AddressType AND
T.UserID = A.UserID)
Thanks
-Mahesh
Seattle

Cascading address returns in query

I have a table with users. These users are joined to an address table
by an intermediary table called user_address, which maps the
addressids to the userids. A user can have multiple addresses,
denoted in the Address table by an int identifying the type of
address.
Here's my issue...
I need to return a list of users with their addresses. but... I need
only to see the user's name once. If they have an address type = 1,
then i need that returned. If they don't have an addresstype 1, then
I need the address for the addresstype 2, etc. for four types of
addresses (head office, physical, billing, other).
Here's what the tables look like:
User_Address
UserID int
AddressID int
Address
AddressID int
Address1 varchar(100)
Address2 varchar(100)
Address3 varchar(100)
Address4 varchar(100)
City nvarchar(100)
PostalCode varchar(10)
Country smallint
AddressType int
CreateDate smalldatetime
CreateUserID int
Updatedate smalldatetime
UpdateUserID int
ProvinceType int
Inactive bit
The Users table has the standard user info, as well as a companyid. I
basically need all the contacts for a company, with the cascading
if... else looping statement for the address.
I haven't been able to find anything online about this... any help is
greatly appreciated.
StacyOn Feb 28, 8:22 pm, "CalgaryDataGrl" <calgarydata...@.gmail.com> wrote:
> I have a table with users. These users are joined to an address table
> by an intermediary table called user_address, which maps the
> addressids to the userids. A user can have multiple addresses,
> denoted in the Address table by an int identifying the type of
> address.
> Here's my issue...
> I need to return a list of users with their addresses. but... I need
> only to see the user's name once. If they have an address type = 1,
> then i need that returned. If they don't have an addresstype 1, then
> I need the address for the addresstype 2, etc. for four types of
> addresses (head office, physical, billing, other).
> Here's what the tables look like:
> User_Address
> UserID int
> AddressID int
> Address
> AddressID int
> Address1 varchar(100)
> Address2 varchar(100)
> Address3 varchar(100)
> Address4 varchar(100)
> City nvarchar(100)
> PostalCode varchar(10)
> Country smallint
> AddressType int
> CreateDate smalldatetime
> CreateUserID int
> Updatedate smalldatetime
> UpdateUserID int
> ProvinceType int
> Inactive bit
> The Users table has the standard user info, as well as a companyid. I
> basically need all the contacts for a company, with the cascading
> if... else looping statement for the address.
> I haven't been able to find anything online about this... any help is
> greatly appreciated.
> Stacy
Can you see if this works for you?
Declare @.User table(UserID int, UserName varchar(50))
Declare @.UserAddress table(UserID int, AddressType int, Address
varchar(50))
insert into @.User values(1,'Emp1')
insert into @.User values(2,'Emp2')
insert into @.User values(3,'Emp3')
insert into @.User values(4,'Emp4')
insert into @.User values(5,'Emp5')
insert into @.UserAddress values(1,1,'Addr11')
insert into @.UserAddress values(1,2,'Addr12')
insert into @.UserAddress values(1,3,'Addr13')
insert into @.UserAddress values(1,4,'Addr14')
insert into @.UserAddress values(2,1,'Addr21')
insert into @.UserAddress values(2,2,'Addr22')
insert into @.UserAddress values(2,3,'Addr23')
insert into @.UserAddress values(3,2,'Addr32')
insert into @.UserAddress values(3,3,'Addr33')
insert into @.UserAddress values(4,1,'Addr41')
insert into @.UserAddress values(4,3,'Addr43')
insert into @.UserAddress values(4,4,'Addr44')
insert into @.UserAddress values(5,4,'Addr54')
Select T.UserID, T.AddressType, U.UserName, A.Address from
(Select Distinct(A.UserID) as UserID, min(A.AddressType)as AddressType
from @.UserAddress A
Group by A.UserID) T
Inner Join @.User U ON U.UserID=T.UserID
INNER JOIN @.UserAddress A ON (A.AddressType = T.AddressType AND
T.UserID = A.UserID)
Thanks
-Mahesh
Seattle

Cascading address returns in query

I have a table with users. These users are joined to an address table
by an intermediary table called user_address, which maps the
addressids to the userids. A user can have multiple addresses,
denoted in the Address table by an int identifying the type of
address.
Here's my issue...
I need to return a list of users with their addresses. but... I need
only to see the user's name once. If they have an address type = 1,
then i need that returned. If they don't have an addresstype 1, then
I need the address for the addresstype 2, etc. for four types of
addresses (head office, physical, billing, other).
Here's what the tables look like:
User_Address
UserID int
AddressID int
Address
AddressID int
Address1 varchar(100)
Address2 varchar(100)
Address3 varchar(100)
Address4 varchar(100)
City nvarchar(100)
PostalCode varchar(10)
Country smallint
AddressType int
CreateDate smalldatetime
CreateUserID int
Updatedate smalldatetime
UpdateUserID int
ProvinceType int
Inactive bit
The Users table has the standard user info, as well as a companyid. I
basically need all the contacts for a company, with the cascading
if... else looping statement for the address.
I haven't been able to find anything online about this... any help is
greatly appreciated.
StacyOn Feb 28, 8:22 pm, "CalgaryDataGrl" <calgarydata...@.gmail.com> wrote:
> I have a table with users. These users are joined to an address table
> by an intermediary table called user_address, which maps the
> addressids to the userids. A user can have multiple addresses,
> denoted in the Address table by an int identifying the type of
> address.
> Here's my issue...
> I need to return a list of users with their addresses. but... I need
> only to see the user's name once. If they have an address type = 1,
> then i need that returned. If they don't have an addresstype 1, then
> I need the address for the addresstype 2, etc. for four types of
> addresses (head office, physical, billing, other).
> Here's what the tables look like:
> User_Address
> UserID int
> AddressID int
> Address
> AddressID int
> Address1 varchar(100)
> Address2 varchar(100)
> Address3 varchar(100)
> Address4 varchar(100)
> City nvarchar(100)
> PostalCode varchar(10)
> Country smallint
> AddressType int
> CreateDate smalldatetime
> CreateUserID int
> Updatedate smalldatetime
> UpdateUserID int
> ProvinceType int
> Inactive bit
> The Users table has the standard user info, as well as a companyid. I
> basically need all the contacts for a company, with the cascading
> if... else looping statement for the address.
> I haven't been able to find anything online about this... any help is
> greatly appreciated.
> Stacy
Can you see if this works for you?
Declare @.User table(UserID int, UserName varchar(50))
Declare @.UserAddress table(UserID int, AddressType int, Address
varchar(50))
insert into @.User values(1,'Emp1')
insert into @.User values(2,'Emp2')
insert into @.User values(3,'Emp3')
insert into @.User values(4,'Emp4')
insert into @.User values(5,'Emp5')
insert into @.UserAddress values(1,1,'Addr11')
insert into @.UserAddress values(1,2,'Addr12')
insert into @.UserAddress values(1,3,'Addr13')
insert into @.UserAddress values(1,4,'Addr14')
insert into @.UserAddress values(2,1,'Addr21')
insert into @.UserAddress values(2,2,'Addr22')
insert into @.UserAddress values(2,3,'Addr23')
insert into @.UserAddress values(3,2,'Addr32')
insert into @.UserAddress values(3,3,'Addr33')
insert into @.UserAddress values(4,1,'Addr41')
insert into @.UserAddress values(4,3,'Addr43')
insert into @.UserAddress values(4,4,'Addr44')
insert into @.UserAddress values(5,4,'Addr54')
Select T.UserID, T.AddressType, U.UserName, A.Address from
(Select Distinct(A.UserID) as UserID, min(A.AddressType)as AddressType
from @.UserAddress A
Group by A.UserID) T
Inner Join @.User U ON U.UserID=T.UserID
INNER JOIN @.UserAddress A ON (A.AddressType = T.AddressType AND
T.UserID = A.UserID)
Thanks
-Mahesh
Seattle

Carriage Returns in Data

I have a ntext field of data. I was trying to use the REPLACE function to
change the carriage returns to spaces, but have not any luck.
Can anyone make any suggestions?
Thank you,
JLFlemingYou don't need the text within <> -- it is just an example to show that
things are working as expected.
Here is an example:
create table #foo (col1 varchar(20))
insert into #foo values ('test')
insert into #foo values ('test
more')
select col1 from #foo
select REPLACE(REPLACE(col1,char(13),'<replace_a>'),char(10),'<replace_b>')
from #foo
Keith
"JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
news:ABB10B20-5FAB-4F3F-9734-0BB29BDD9053@.microsoft.com...
>I have a ntext field of data. I was trying to use the REPLACE function to
> change the carriage returns to spaces, but have not any luck.
> Can anyone make any suggestions?
> Thank you,
> JLFleming|||You will have write a procedure that loops 8000 character chunks of the data
doing the replace.
Thomas
"JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
news:ABB10B20-5FAB-4F3F-9734-0BB29BDD9053@.microsoft.com...
>I have a ntext field of data. I was trying to use the REPLACE function to
> change the carriage returns to spaces, but have not any luck.
> Can anyone make any suggestions?
> Thank you,
> JLFleming|||I missed the ntext bit the first time I read your post.
Keith
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eMF47mHYFHA.4036@.tk2msftngp13.phx.gbl...
> You don't need the text within <> -- it is just an example to show that
> things are working as expected.
> Here is an example:
> create table #foo (col1 varchar(20))
> insert into #foo values ('test')
> insert into #foo values ('test
> more')
> select col1 from #foo
> select
> REPLACE(REPLACE(col1,char(13),'<replace_a>'),char(10),'<replace_b>') from
> #foo
>
> --
> Keith
>
> "JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
> news:ABB10B20-5FAB-4F3F-9734-0BB29BDD9053@.microsoft.com...
>|||The best way to do this is outside of SQL Server. Text data is a beast in
SQL Server 2000 and earlier to deal with in SQL. The chunking idea given by
Thomas is feasible, but you have to be careful about your search value
crossing the chunk boundry.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
news:ABB10B20-5FAB-4F3F-9734-0BB29BDD9053@.microsoft.com...
>I have a ntext field of data. I was trying to use the REPLACE function to
> change the carriage returns to spaces, but have not any luck.
> Can anyone make any suggestions?
> Thank you,
> JLFleming

Carriage Returns become Question Marks in SQL

Hi all,
I tried the ASP .NET forum with no luck on this one. Maybe someone
here will know the answer.
I have an old ASP .NET 1.1 application that I haven't had time to
rebuild with .NET 2.0. No changes have been made to the application
or
the SQL Server the application uses. I have a web form where users
can
type multiple lines of text, and it is entered into an SQL database
(SQL 2000 Enterprise) into a column with a datatype of "text".
Recently (it seems out of nowhere), If my users enter a carriage
return into the webform, it becomes a question mark in the sql
database. It's really a bit funny, but also annoying, lol. Does
anyone
have any idea why this might be happening?
To clarify. I'm typing text into a multiline textbox. For every place
I hit the return key, it is converted into a question mark in SQL.
I'm
using standard .net sql insert commands, and I don't parse or modify
the text string in anyway. The question marks show whether I load the
data back into my webform, or even if I do a SQL query via Query
Analyzer. I'm stumped.
Thanks so much!
Can you post an example insert string (e.g. from the debug window of your
app)? Does it still produce ?s if you paste that string into Query
Analyzer, and execute it manually? If so, take the string, and do this:
DECLARE @.str NVARCHAR(MAX);
SET @.str = 'INSERT string here...';
DECLARE @.i INT;
SET @.i = 1;
WHILE @.i <= LEN(@.str)
BEGIN
PRINT ASCII(SUBSTRING(@.str, @.i, 1));
SET @.i = @.i + 1;
END
My guess is that .NET is injecting non-printing characters and/or not
producing correct CR/LF pairs.
<mattdaddym@.gmail.com> wrote in message
news:79ba1e43-dadf-4551-b6e5-a810cfeaf231@.i3g2000hsf.googlegroups.com...
> Hi all,
> I tried the ASP .NET forum with no luck on this one. Maybe someone
> here will know the answer.
> I have an old ASP .NET 1.1 application that I haven't had time to
> rebuild with .NET 2.0. No changes have been made to the application
> or
> the SQL Server the application uses. I have a web form where users
> can
> type multiple lines of text, and it is entered into an SQL database
> (SQL 2000 Enterprise) into a column with a datatype of "text".
> Recently (it seems out of nowhere), If my users enter a carriage
> return into the webform, it becomes a question mark in the sql
> database. It's really a bit funny, but also annoying, lol. Does
> anyone
> have any idea why this might be happening?
>
> To clarify. I'm typing text into a multiline textbox. For every place
> I hit the return key, it is converted into a question mark in SQL.
> I'm
> using standard .net sql insert commands, and I don't parse or modify
> the text string in anyway. The question marks show whether I load the
> data back into my webform, or even if I do a SQL query via Query
> Analyzer. I'm stumped.
>
> Thanks so much!

Carriage Returns become Question Marks in SQL

Hi all,
I tried the ASP .NET forum with no luck on this one. Maybe someone
here will know the answer.
I have an old ASP .NET 1.1 application that I haven't had time to
rebuild with .NET 2.0. No changes have been made to the application
or
the SQL Server the application uses. I have a web form where users
can
type multiple lines of text, and it is entered into an SQL database
(SQL 2000 Enterprise) into a column with a datatype of "text".
Recently (it seems out of nowhere), If my users enter a carriage
return into the webform, it becomes a question mark in the sql
database. It's really a bit funny, but also annoying, lol. Does
anyone
have any idea why this might be happening?
To clarify. I'm typing text into a multiline textbox. For every place
I hit the return key, it is converted into a question mark in SQL.
I'm
using standard .net sql insert commands, and I don't parse or modify
the text string in anyway. The question marks show whether I load the
data back into my webform, or even if I do a SQL query via Query
Analyzer. I'm stumped.
Thanks so much!Can you post an example insert string (e.g. from the debug window of your
app)? Does it still produce ?s if you paste that string into Query
Analyzer, and execute it manually? If so, take the string, and do this:
DECLARE @.str NVARCHAR(MAX);
SET @.str = 'INSERT string here...';
DECLARE @.i INT;
SET @.i = 1;
WHILE @.i <= LEN(@.str)
BEGIN
PRINT ASCII(SUBSTRING(@.str, @.i, 1));
SET @.i = @.i + 1;
END
My guess is that .NET is injecting non-printing characters and/or not
producing correct CR/LF pairs.
<mattdaddym@.gmail.com> wrote in message
news:79ba1e43-dadf-4551-b6e5-a810cfeaf231@.i3g2000hsf.googlegroups.com...
> Hi all,
> I tried the ASP .NET forum with no luck on this one. Maybe someone
> here will know the answer.
> I have an old ASP .NET 1.1 application that I haven't had time to
> rebuild with .NET 2.0. No changes have been made to the application
> or
> the SQL Server the application uses. I have a web form where users
> can
> type multiple lines of text, and it is entered into an SQL database
> (SQL 2000 Enterprise) into a column with a datatype of "text".
> Recently (it seems out of nowhere), If my users enter a carriage
> return into the webform, it becomes a question mark in the sql
> database. It's really a bit funny, but also annoying, lol. Does
> anyone
> have any idea why this might be happening?
>
> To clarify. I'm typing text into a multiline textbox. For every place
> I hit the return key, it is converted into a question mark in SQL.
> I'm
> using standard .net sql insert commands, and I don't parse or modify
> the text string in anyway. The question marks show whether I load the
> data back into my webform, or even if I do a SQL query via Query
> Analyzer. I'm stumped.
>
> Thanks so much!

Carriage Returns and Line Breaks

I have an address field that is coming back as a single field with carriage returns and line breaks and I would like to have it properly wrap in a single box, but the wrapping is all off. How can I get this to properly break? ThanksMake sure you have text box property "cangrow" to true.|||

Nope. That is not the issue. It is cangrow = true.

What I want is to take a field that comes out as:

John Smith CR LB 123 Main Street CR LB Anytown, MA 01888 CR LB USA

as

John Smith

123 Main Street

Anytown, MA 01888

USA

What I am getting is:

John Smith 123

Main Street Anytown,

MA 01888 USA

So I am looking how to read the CR and LBs and maybe replace them with BR tags, not sure.

|||Also... what is being returned to indicate the line break are char(13)s. I am recreating a report that I had lost and was able to resolve this at one time, but forget how I got around it.|||

try select field1 + char(13) + char(10) + field2

Carriage returns

I'm migrating from Crystal. When I inserted address fields which contained
carriage returns, Crystal seemed to recognise them automatically, putting
the second line beneath the first line, etc. RS doesn't do this - obviously
I have to do something more explicit. Cam anybody help?
ThanksFirst: You need SP1. Second: I've only had success with carriage returns
when there physically was a CRLF pair. With the HTML output, the <BR> tags
are not rendered unless said CRLF pair exist.
--
TIM ELLISON
"Microsoft" <mitchellpaul@.blueyonder.co.uk> wrote in message
news:%23Kkhi714EHA.524@.TK2MSFTNGP09.phx.gbl...
> I'm migrating from Crystal. When I inserted address fields which contained
> carriage returns, Crystal seemed to recognise them automatically, putting
> the second line beneath the first line, etc. RS doesn't do this -
obviously
> I have to do something more explicit. Cam anybody help?
> Thanks
>|||Seem to have got this sorted with an expression:
=replace((Fields!AD_ADDRESS.Value),chr(13),vbcrlf)
Thanks for the help anyway, will get SP1 installed pronto...
"Tim" <TimmyDotNet@.direcway.com> wrote in message
news:eINaxi24EHA.1976@.TK2MSFTNGP09.phx.gbl...
> First: You need SP1. Second: I've only had success with carriage returns
> when there physically was a CRLF pair. With the HTML output, the <BR>
> tags
> are not rendered unless said CRLF pair exist.
> --
> TIM ELLISON
> "Microsoft" <mitchellpaul@.blueyonder.co.uk> wrote in message
> news:%23Kkhi714EHA.524@.TK2MSFTNGP09.phx.gbl...
>> I'm migrating from Crystal. When I inserted address fields which
>> contained
>> carriage returns, Crystal seemed to recognise them automatically, putting
>> the second line beneath the first line, etc. RS doesn't do this -
> obviously
>> I have to do something more explicit. Cam anybody help?
>> Thanks
>>
>

Carriage Returns

I have a SQL table with a "Text" column in it. The report does not show
the carriage returns in the text, making it very hard to read.
I tried changing my SELECT to do a REPLACE(tblProject.ShortDesc, CHAR(10),
'<BR>') and this actually printed out the literal '<BR>' in the report.
Any ideas?
Thanks,,, CliffTry vbcrlf.

Carriage Returns

Hi,
I have an access database that has been created by the export utility
from Outlook contacts. In outlook contacts there is a free text field
where you can enter paragraphs, bold text, etc. Now this field in
Access is there without the styles but the carriage returns still
exist. My problem is when importing this access table to sql server, it
removes all this carriage returns and puts a blank instead.
Any ideas?
Thanks in advance,
Shahid<shahid.juma@.gmail.com> wrote in message
news:1121110803.051125.153310@.o13g2000cwo.googlegroups.com...
> Hi,
> I have an access database that has been created by the export utility
> from Outlook contacts. In outlook contacts there is a free text field
> where you can enter paragraphs, bold text, etc. Now this field in
> Access is there without the styles but the carriage returns still
> exist. My problem is when importing this access table to sql server, it
> removes all this carriage returns and puts a blank instead.
If this is a one-time shot, write a script that replaces the CR/LF in all
the memo fields with some other textual. Once imported in SQL, replace it
with CR/LF again.
This is just a work-around since I neither know the explanation and nor a
"real" solution ;-)
Christoph

Carriage Returns

Hi,
I have an access database that has been created by the export utility
from Outlook contacts. In outlook contacts there is a free text field
where you can enter paragraphs, bold text, etc. Now this field in
Access is there without the styles but the carriage returns still
exist. My problem is when importing this access table to sql server, it
removes all this carriage returns and puts a blank instead.
Any ideas?
Thanks in advance,
Shahid
<shahid.juma@.gmail.com> wrote in message
news:1121110803.051125.153310@.o13g2000cwo.googlegr oups.com...
> Hi,
> I have an access database that has been created by the export utility
> from Outlook contacts. In outlook contacts there is a free text field
> where you can enter paragraphs, bold text, etc. Now this field in
> Access is there without the styles but the carriage returns still
> exist. My problem is when importing this access table to sql server, it
> removes all this carriage returns and puts a blank instead.
If this is a one-time shot, write a script that replaces the CR/LF in all
the memo fields with some other textual. Once imported in SQL, replace it
with CR/LF again.
This is just a work-around since I neither know the explanation and nor a
"real" solution ;-)
Christoph

Carriage Returns

Hi,
I have an access database that has been created by the export utility
from Outlook contacts. In outlook contacts there is a free text field
where you can enter paragraphs, bold text, etc. Now this field in
Access is there without the styles but the carriage returns still
exist. My problem is when importing this access table to sql server, it
removes all this carriage returns and puts a blank instead.
Any ideas?
Thanks in advance,
Shahid<shahid.juma@.gmail.com> wrote in message
news:1121110803.051125.153310@.o13g2000cwo.googlegroups.com...
> Hi,
> I have an access database that has been created by the export utility
> from Outlook contacts. In outlook contacts there is a free text field
> where you can enter paragraphs, bold text, etc. Now this field in
> Access is there without the styles but the carriage returns still
> exist. My problem is when importing this access table to sql server, it
> removes all this carriage returns and puts a blank instead.
If this is a one-time shot, write a script that replaces the CR/LF in all
the memo fields with some other textual. Once imported in SQL, replace it
with CR/LF again.
This is just a work-around since I neither know the explanation and nor a
"real" solution ;-)
Christoph

Carriage Return in Data

I am inserting data into a field that is setup as the datatype ntext and
would like to place carriage returns in the text to format the data.
For example:
This is<new line>my data. (Where <new line> is the code for a new line.)
Would display as:
This is
my data.
I tried using VBCrLf and Chr(13) & Chr(10), but neither worked.
Thanks,
MikeYou can add them outside if you wish:
insert <tablename> values ('Here is some text' + char(13) + char(10) + 'and
some additional text on a second line')
Rick Sawtell
MCT, MCSD, MCDBA
"Mike" <mbaith@.yahoo.com> wrote in message
news:etOtCp4iEHA.4092@.TK2MSFTNGP10.phx.gbl...
> I am inserting data into a field that is setup as the datatype ntext and
> would like to place carriage returns in the text to format the data.
> For example:
> This is<new line>my data. (Where <new line> is the code for a new line.)
> Would display as:
> This is
> my data.
> I tried using VBCrLf and Chr(13) & Chr(10), but neither worked.
> Thanks,
> Mike
>|||Rick,
I have tried using Chr(13) & Chr(10), but it doesn't work. When outputing it
all appears on the same line.
Thanks,
Mike|||When outputting it where? I ran this in query analyzer.
========================================
CREATE TABLE Frog (col1 ntext)
GO
INSERT Frog VALUES ('Line 1' + CHAR(13) + CHAR(10) + 'Line 2' + CHAR(13) +
CHAR(10) + 'Line 3')
GO
SELECT col1 FROM Frog
col1
----
---Line 1
Line 2
Line 3
(1 row(s) affected)|||Can you post a repro? Below work just fine in my query analyzer:
SELECT 'Hello ' + CHAR(13) + CHAR(10) + 'there!'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike" <mbaith@.yahoo.com> wrote in message news:Oy39A14iEHA.596@.TK2MSFTNGP11.phx.gbl...[vbco
l=seagreen]
> Rick,
> I have tried using Chr(13) & Chr(10), but it doesn't work. When outputing
it
> all appears on the same line.
> Thanks,
> Mike
>[/vbcol]|||Rick,
I was outputting it to a web page, which apparently ignores char 13 & 10...
I used a replace to replace the char 13 & 10 with a <br> and its working. (I
couldn't put the <br> in the data because the webpage isn't the only
application accessing the data.)
Thanks for your help!
Mike

Carriage Return in Data

I am inserting data into a field that is setup as the datatype ntext and
would like to place carriage returns in the text to format the data.
For example:
This is<new line>my data. (Where <new line> is the code for a new line.)
Would display as:
This is
my data.
I tried using VBCrLf and Chr(13) & Chr(10), but neither worked.
Thanks,
Mike
You can add them outside if you wish:
insert <tablename> values ('Here is some text' + char(13) + char(10) + 'and
some additional text on a second line')
Rick Sawtell
MCT, MCSD, MCDBA
"Mike" <mbaith@.yahoo.com> wrote in message
news:etOtCp4iEHA.4092@.TK2MSFTNGP10.phx.gbl...
> I am inserting data into a field that is setup as the datatype ntext and
> would like to place carriage returns in the text to format the data.
> For example:
> This is<new line>my data. (Where <new line> is the code for a new line.)
> Would display as:
> This is
> my data.
> I tried using VBCrLf and Chr(13) & Chr(10), but neither worked.
> Thanks,
> Mike
>
|||Rick,
I have tried using Chr(13) & Chr(10), but it doesn't work. When outputing it
all appears on the same line.
Thanks,
Mike
|||When outputting it where? I ran this in query analyzer.
========================================
CREATE TABLE Frog (col1 ntext)
GO
INSERT Frog VALUES ('Line 1' + CHAR(13) + CHAR(10) + 'Line 2' + CHAR(13) +
CHAR(10) + 'Line 3')
GO
SELECT col1 FROM Frog
col1
---Line 1
Line 2
Line 3
(1 row(s) affected)
|||Can you post a repro? Below work just fine in my query analyzer:
SELECT 'Hello ' + CHAR(13) + CHAR(10) + 'there!'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike" <mbaith@.yahoo.com> wrote in message news:Oy39A14iEHA.596@.TK2MSFTNGP11.phx.gbl...
> Rick,
> I have tried using Chr(13) & Chr(10), but it doesn't work. When outputing it
> all appears on the same line.
> Thanks,
> Mike
>
|||Rick,
I was outputting it to a web page, which apparently ignores char 13 & 10...
I used a replace to replace the char 13 & 10 with a <br> and its working. (I
couldn't put the <br> in the data because the webpage isn't the only
application accessing the data.)
Thanks for your help!
Mike

Carriage Return in Data

I am inserting data into a field that is setup as the datatype ntext and
would like to place carriage returns in the text to format the data.
For example:
This is<new line>my data. (Where <new line> is the code for a new line.)
Would display as:
This is
my data.
I tried using VBCrLf and Chr(13) & Chr(10), but neither worked.
Thanks,
MikeYou can add them outside if you wish:
insert <tablename> values ('Here is some text' + char(13) + char(10) + 'and
some additional text on a second line')
Rick Sawtell
MCT, MCSD, MCDBA
"Mike" <mbaith@.yahoo.com> wrote in message
news:etOtCp4iEHA.4092@.TK2MSFTNGP10.phx.gbl...
> I am inserting data into a field that is setup as the datatype ntext and
> would like to place carriage returns in the text to format the data.
> For example:
> This is<new line>my data. (Where <new line> is the code for a new line.)
> Would display as:
> This is
> my data.
> I tried using VBCrLf and Chr(13) & Chr(10), but neither worked.
> Thanks,
> Mike
>|||Rick,
I have tried using Chr(13) & Chr(10), but it doesn't work. When outputing it
all appears on the same line.
Thanks,
Mike|||When outputting it where? I ran this in query analyzer.
========================================
CREATE TABLE Frog (col1 ntext)
GO
INSERT Frog VALUES ('Line 1' + CHAR(13) + CHAR(10) + 'Line 2' + CHAR(13) +
CHAR(10) + 'Line 3')
GO
SELECT col1 FROM Frog
col1
----
---Line 1
Line 2
Line 3
(1 row(s) affected)|||Can you post a repro? Below work just fine in my query analyzer:
SELECT 'Hello ' + CHAR(13) + CHAR(10) + 'there!'
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike" <mbaith@.yahoo.com> wrote in message news:Oy39A14iEHA.596@.TK2MSFTNGP11.phx.gbl...
> Rick,
> I have tried using Chr(13) & Chr(10), but it doesn't work. When outputing it
> all appears on the same line.
> Thanks,
> Mike
>|||Rick,
I was outputting it to a web page, which apparently ignores char 13 & 10...
I used a replace to replace the char 13 & 10 with a <br> and its working. (I
couldn't put the <br> in the data because the webpage isn't the only
application accessing the data.)
Thanks for your help!
Mike

Carriage return in column values

Hi,
How can I find out where the carriage returns are in a column value in a
table? For example, we have an Address column in a Customer table. This
column value can have a maximum of 255 characters where carriage returns are
allowed. How can I find out where those carriage returns are?
Thank you in advance,
DeeOne way is using CHARINDEX, if you only want to first position:
USE tempdb
CREATE TABLE t(c1 int identity, c2 varchar(2000))
INSERT INTO t VALUES('Hello
there')
INSERT INTO t VALUES('Hi everybody
I''ts Doctor Nick')
SELECT CHARINDEX(CHAR(13) + CHAR(10), c2) FROM t
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bpdee" <bpdee@.discussions.microsoft.com> wrote in message
news:0EF8E157-C008-4370-A355-DB9E43A3F741@.microsoft.com...
> Hi,
> How can I find out where the carriage returns are in a column value in a
> table? For example, we have an Address column in a Customer table. This
> column value can have a maximum of 255 characters where carriage returns are
> allowed. How can I find out where those carriage returns are?
> Thank you in advance,
> Dee|||Thank you, Tibor, for your response. Let's put a different twist to it now.
Let's say
I want to identify customer records where the address has 1 or more lines in
the Address column that are greater than 40 characters. Obviously, the text
before each carriage return is considered as one line in the Address column.
"Tibor Karaszi" wrote:
> One way is using CHARINDEX, if you only want to first position:
> USE tempdb
> CREATE TABLE t(c1 int identity, c2 varchar(2000))
> INSERT INTO t VALUES('Hello
> there')
> INSERT INTO t VALUES('Hi everybody
> I''ts Doctor Nick')
> SELECT CHARINDEX(CHAR(13) + CHAR(10), c2) FROM t
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "bpdee" <bpdee@.discussions.microsoft.com> wrote in message
> news:0EF8E157-C008-4370-A355-DB9E43A3F741@.microsoft.com...
> > Hi,
> >
> > How can I find out where the carriage returns are in a column value in a
> > table? For example, we have an Address column in a Customer table. This
> > column value can have a maximum of 255 characters where carriage returns are
> > allowed. How can I find out where those carriage returns are?
> >
> > Thank you in advance,
> > Dee
>|||Dealing with unknowns (like unknown number of address lines), I'd suggest writing a scalar user
defined function for this.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bpdee" <bpdee@.discussions.microsoft.com> wrote in message
news:1F0AF68D-40DA-4F7A-9C84-C086922573FE@.microsoft.com...
> Thank you, Tibor, for your response. Let's put a different twist to it now.
> Let's say
> I want to identify customer records where the address has 1 or more lines in
> the Address column that are greater than 40 characters. Obviously, the text
> before each carriage return is considered as one line in the Address column.
> "Tibor Karaszi" wrote:
>> One way is using CHARINDEX, if you only want to first position:
>> USE tempdb
>> CREATE TABLE t(c1 int identity, c2 varchar(2000))
>> INSERT INTO t VALUES('Hello
>> there')
>> INSERT INTO t VALUES('Hi everybody
>> I''ts Doctor Nick')
>> SELECT CHARINDEX(CHAR(13) + CHAR(10), c2) FROM t
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "bpdee" <bpdee@.discussions.microsoft.com> wrote in message
>> news:0EF8E157-C008-4370-A355-DB9E43A3F741@.microsoft.com...
>> > Hi,
>> >
>> > How can I find out where the carriage returns are in a column value in a
>> > table? For example, we have an Address column in a Customer table. This
>> > column value can have a maximum of 255 characters where carriage returns are
>> > allowed. How can I find out where those carriage returns are?
>> >
>> > Thank you in advance,
>> > Dee
>>

Carriage Return Bug in Management Studio?

I'm using SQL Server 2005 Manage Studio to edit some varchar and text fields
I have in my database. I want to insert carriage returns in my text, and I
tried CTRL+Enter which works fine in SQL 2000, but it doesn't seem to work in
Management Studio. Further more, when I import data from my SQL 2000 and then
open the table for editing in Management Studio I find that all the carriage
returns are not visible. I say not visible, because I know they're still
there because my application reads them fine. Is this a UI bug in Management
Studio?!!! Anyone knows of a fix or work around? Thanks.
Hi, Waleed,
+char(13)+char(10) could insert a carriage return and line feed.
Ta,
Yifei
"Waleed Abdulla xrules org>" <waleed_abdulla <atdot> wrote in message
news:E59CC994-B548-45CE-837A-E6C71A366C5F@.microsoft.com...
> I'm using SQL Server 2005 Manage Studio to edit some varchar and text
fields
> I have in my database. I want to insert carriage returns in my text, and I
> tried CTRL+Enter which works fine in SQL 2000, but it doesn't seem to work
in
> Management Studio. Further more, when I import data from my SQL 2000 and
then
> open the table for editing in Management Studio I find that all the
carriage
> returns are not visible. I say not visible, because I know they're still
> there because my application reads them fine. Is this a UI bug in
Management
> Studio?!!! Anyone knows of a fix or work around? Thanks.
|||Yifei,
Thanks, but you're answering the wrong question. As I mentioned, reading
and inserting carriage returns programmatically (char 13 + char 10) is no
problem. My issue is in the UI when trying to type in carriage returns in the
data directly in the table view in Management Studio.
Waleed
"Yifei Jiang" wrote:

> Hi, Waleed,
> +char(13)+char(10) could insert a carriage return and line feed.
> Ta,
> Yifei
> "Waleed Abdulla xrules org>" <waleed_abdulla <atdot> wrote in message
> news:E59CC994-B548-45CE-837A-E6C71A366C5F@.microsoft.com...
> fields
> in
> then
> carriage
> Management
>
>