combine to varchar field when on is null

When we combine to varchar field when on is null,
like
Select FirstName+ LastName as FullName..
if FirstName is null, then FullName will be null.
How can we combine to varchar field when on is null, and the result will be
the non-null field?Select COALESCE(FirstName, '') + COALESCE(LastName, '') as FullName
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ad" <ad@.wfes.tcc.edu.tw> wrote in message news:OPgSvFCQFHA.3716@.TK2MSFTNGP14.phx.gbl...
> When we combine to varchar field when on is null,
> like
> Select FirstName+ LastName as FullName..
> if FirstName is null, then FullName will be null.
> How can we combine to varchar field when on is null, and the result will be
> the non-null field?
>
>|||Select ISNULL(FirstName,'')+ ISNULL(LastName,'')as FullName..
HTH, Jens Süßmeyer.
--
http://www.sqlserver2005.de
--
"ad" <ad@.wfes.tcc.edu.tw> schrieb im Newsbeitrag
news:OPgSvFCQFHA.3716@.TK2MSFTNGP14.phx.gbl...
> When we combine to varchar field when on is null,
> like
> Select FirstName+ LastName as FullName..
> if FirstName is null, then FullName will be null.
> How can we combine to varchar field when on is null, and the result will
> be
> the non-null field?
>
>

combine to varchar field when on is null

When we combine to varchar field when on is null,
like
Select FirstName+ LastName as FullName..
if FirstName is null, then FullName will be null.
How can we combine to varchar field when on is null, and the result will be
the non-null field?
Select COALESCE(FirstName, '') + COALESCE(LastName, '') as FullName
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ad" <ad@.wfes.tcc.edu.tw> wrote in message news:OPgSvFCQFHA.3716@.TK2MSFTNGP14.phx.gbl...
> When we combine to varchar field when on is null,
> like
> Select FirstName+ LastName as FullName..
> if FirstName is null, then FullName will be null.
> How can we combine to varchar field when on is null, and the result will be
> the non-null field?
>
>
|||Select ISNULL(FirstName,'')+ ISNULL(LastName,'')as FullName..
HTH, Jens Smeyer.
http://www.sqlserver2005.de
"ad" <ad@.wfes.tcc.edu.tw> schrieb im Newsbeitrag
news:OPgSvFCQFHA.3716@.TK2MSFTNGP14.phx.gbl...
> When we combine to varchar field when on is null,
> like
> Select FirstName+ LastName as FullName..
> if FirstName is null, then FullName will be null.
> How can we combine to varchar field when on is null, and the result will
> be
> the non-null field?
>
>

combine to varchar field when on is null

When we combine to varchar field when on is null,
like
Select FirstName+ LastName as FullName..
if FirstName is null, then FullName will be null.
How can we combine to varchar field when on is null, and the result will be
the non-null field?Select COALESCE(FirstName, '') + COALESCE(LastName, '') as FullName
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ad" <ad@.wfes.tcc.edu.tw> wrote in message news:OPgSvFCQFHA.3716@.TK2MSFTNGP14.phx.gbl...[vbc
ol=seagreen]
> When we combine to varchar field when on is null,
> like
> Select FirstName+ LastName as FullName..
> if FirstName is null, then FullName will be null.
> How can we combine to varchar field when on is null, and the result will b
e
> the non-null field?
>
>[/vbcol]|||Select ISNULL(FirstName,'')+ ISNULL(LastName,'')as FullName..
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"ad" <ad@.wfes.tcc.edu.tw> schrieb im Newsbeitrag
news:OPgSvFCQFHA.3716@.TK2MSFTNGP14.phx.gbl...
> When we combine to varchar field when on is null,
> like
> Select FirstName+ LastName as FullName..
> if FirstName is null, then FullName will be null.
> How can we combine to varchar field when on is null, and the result will
> be
> the non-null field?
>
>

Combine Rows in Search Result

In Sql Server 2005 Express I have this table:

CREATE TABLE [dbo].[Sections](
[SectionID] [int] NOT NULL,
[DocumentNo] [smallint] NULL,
[SequenceNo] [smallint] NULL,
[SectionNo] [smallint] NULL,
[DocumentTypeID] [smallint] NULL,
[SectionText] [ntext] NULL)

Each paragraph of text (SectionText) is in its own row(SectionNo) Each primary document has a DocumentTypeID of 1 withthree subdocument types (2=Index, 3=Background, 4=Report).

I run this query and return a collection of single rows from various documents grouped together by DocumentNo:

SELECT *
FROM Sections
WHERE CONTAINS (SectionText, 'exercise')
ORDER BY DocumentNo

For each row that contains the search term, I would like toreturn the full document (all rows as parapraphs within one row ofreturned data). In other words, I want to reconstitute the fulldocument as it existed prior to being inserted into the database withparagraph separation.

For exampe, if the search term is in row 3of DocumentNo=5, DocumentTypeID=2, I want to return all the rows ofthat document in one block of text that retains paragraph format(preferablly with a line break and carriage return betweenparagraphs). How can this be done?

You can do this trick which will lead you to solve the problem.

Okay, let say you need to group each page's paragraph in one record insted of many records (as in your current case).

Step#1:

So, Create another table with following columns :
1) BookID: Int or smallint
2) PageID: Int or smallint
3) PageText: Text or NText

Step#2:

1) Do acursor that will loop throug all of theparagraphs related to aspecific page.
2) DoINSERT thefirst record into thePageText field of thenew created table, while you doUPDATEfor therest of recordsafter concatenatingthem with value already exists in thePageText field.

Step#3:

Do this for each page in each book.

Result:

At the end you will have one table from which you can query and seach about any word/paragraph in any page in any book!!

Good luck.

|||

Thanks for the suggestion. I will give it a try.

Columns [not null]

hi, I have a problem with a flat file and a table

I have a flat file with 5 columns and in the table I have 30 columns and they all are (not null)
I cannot leave other columns in target for which it throws mistake, have to insert something, since as I insert information in the demas columns?

Since I can solve this problem?

helps please

Use a derived column transform to create default values for each column not in text file|||

TheViewMaster wrote:

Use a derived column transform to create default values for each column not in text file

Thank you very much, I work perfectly

|||Glad to be of assistance
Please mark my previous post as an answer if you have a chance - need to make it to top asnwerererers

Column Invisibility

Hey,

I am retrieving values from a database I have setup in sql server. Basically I want to make a column invisible if the value is null.

Here is the code I tried, but it didn't work.

=IIf(Fields!FundingCode.Valueisnothing,True, False)

I would greatly appreciate the help

a column of what? Datagrid, Gridview? What?

Column Count Percentage of Not Null fields

Hello folks,

I am stuck at a problem, not sure on how to go about writing a query that will return as a percentage the number of fields in a row that are null.

For instance, a row from my table:
Row1 : field1 field2 field3

If field3 is empty or null, my query should return 67%.

So far I have gotten the number of fields:
select count(1) from information_schema.columns where table_name='myTable'

I could loop through the fields but I am sure there is a simpler way of doing it, I have seen something simpler in the past with some builtin SQL functions. I am using MS SQL 2005.

Thanks for your help
Mikelet me get this straight -- you don't really know which columns the table will have?|||Do you want to inspect the content of each column in each row OR
Is your intention instead to find the number of columns in a table that do not allow nulls?

select object_name(id),colcount=count(*)
,notnullablecount=sum(case when isnullable=0 then 1 else 0 end)
,nullablecount=sum(case when isnullable=1 then 1 else 0 end)
,'Notnullable%'=convert(int,sum(case when isnullable=0 then 1 else 0 end)*100./count(*)+.5)
from syscolumns
where id=object_id('myTable')
group by id

Column allows nulls i want to change not to allow

When i do a select on my emplee table for rows with null idCompany i dont ge
t
any records
I then try to modify the table to not allow a null idCompany and i get this
error message:
'Employee (aMgmt)' table
- Unable to modify table.
Cannot insert the value NULL into column 'idCompany', table
'D2.aMgmt.Tmp_Employee'; column does not allow nulls. INSERT fails.
The statement has been terminated.
This suxis you select like this?
SELECT * FROM D2.aMgmt.Tmp_Employee
WHERE idCompany IS NOT NULL
or do you have
WHERE idCompany <> NULL
because that will return no rows unless ANSI_NULLS is set to OFF
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Try specifying a default value for idCompany when issuing the ALTER TABLE
statement.
ALTER TABLE employee ALTER COLUMN idCompany datatype NOT NULL DEFAULT ''
"thom" wrote:

> When i do a select on my emplee table for rows with null idCompany i dont
get
> any records
> I then try to modify the table to not allow a null idCompany and i get thi
s
> error message:
> 'Employee (aMgmt)' table
> - Unable to modify table.
> Cannot insert the value NULL into column 'idCompany', table
> 'D2.aMgmt.Tmp_Employee'; column does not allow nulls. INSERT fails.
> The statement has been terminated.
> This sux|||I hope you wrote the query as
select * from tbl1
where dCompany is null -- and not "dCompany = null"|||How did you write the query to select all records having null idcompany.
This should list all records with null idcompany
Select * from Employee where idcompany IS NULL
thom wrote:
> When i do a select on my emplee table for rows with null idCompany i dont
get
> any records
> I then try to modify the table to not allow a null idCompany and i get thi
s
> error message:
> 'Employee (aMgmt)' table
> - Unable to modify table.
> Cannot insert the value NULL into column 'idCompany', table
> 'D2.aMgmt.Tmp_Employee'; column does not allow nulls. INSERT fails.
> The statement has been terminated.
> This sux|||Of course the IS NOT NULL should be IS NULL and the <> NULL should be
= NULL, sorry for that
so
SELECT * FROM D2.aMgmt.Tmp_Employee
WHERE idCompany IS NULL
or do you have
WHERE idCompany = NULL
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Sorry wrong post :P|||I Dont know how but i fixed it, i was not using <> null and not using = nul
l
This issue seemed to be with the Check existing data on creation, i ended up
getting that message on another table and when i changed check existing data
on create to no there was no problem.
BUT either way when i SELECTED * FROM myTable WHERE idColumn IS NULL
i got no records returned
"thom" wrote:

> When i do a select on my emplee table for rows with null idCompany i dont
get
> any records
> I then try to modify the table to not allow a null idCompany and i get thi
s
> error message:
> 'Employee (aMgmt)' table
> - Unable to modify table.
> Cannot insert the value NULL into column 'idCompany', table
> 'D2.aMgmt.Tmp_Employee'; column does not allow nulls. INSERT fails.
> The statement has been terminated.
> This sux|||Was it really?
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:840FB234-5802-4A53-BEE6-D36963CE17F7@.microsoft.com...
> Sorry wrong post :P|||No it wasn't. This was posted in the wrong thread...
and thanks for finding this.. I was searching for this post :)

Column allows nulls I want to change no nulls allowed

When i do a select on my emplee table for rows with null idCompany i dont get any records

I then try to modify the table to not allow a null idCompany and i get this error message:

'Employee (aMgmt)' table
- Unable to modify table.
Cannot insert the value NULL into column 'idCompany', table 'D2.aMgmt.Tmp_Employee'; column does not allow nulls. INSERT fails.
The statement has been terminated.

This suxWhat does a SELECT statement not returning any records have to do with allowing NULLs? And why are you doing an INSERT statment while modifying the table?

Your post does not make much sense.|||It would help if you poseted the 'select' statement.

Are you using columnname = NULL or columnname IS NULL in the 'where' clause?

I don't think you can alter a table to prohibit NULLs if NULLs already exist in the table. You'll have to change those values or provide a default when you do the alter.|||This issue seemed to be with the Check existing data on creation, i ended up getting that message on another table and when i changed check existing data on create to no there was no problem.

BUT either way when i SELECTED * FROM myTable WHERE idColumn IS NULL
i got no records returned

HERE IS THE SQL On the 2nd table problem

SELECT idMaster, idVendor, idExpType, idCompany, DateEntered, EnteredBy, CheckNumber, CheckAmount, VendorName, ExpenseType, CheckDate, voided,
ckSuffix, MasterTs, tempCol
FROM aDataEntry.master
WHERE (idCompany IS NULL)|||Post the script (DDL) for the table.

Column Aliases

I want to refere to an aliased column in an expression in another column. I
have one called get getforename and I want to test if it is null and display
a different value but all's I get is
Invalid column name 'getforerank'. Regards, Chris.
SELECT
dbo.tblCemeteries.CemeteryName,
Surname,
age,
dod,
Forename,
CASE WHEN tblNames.Forename='liz' THEN 0
else(SELECT Rank FROM dbo.tblKeynames WHERE (KeyName = Forename))
END as getforerank,
case when getforerank is null then 1000
else getforerank
end,
CASE WHEN tblNames.Surname='smith' THEN 0
else(SELECT Rank FROM dbo.tblKeynames WHERE (KeyName = Surname))
END as getrank FROM dbo.tblNames INNER JOIN dbo.tblCemeteries
ON dbo.tblNames.Cemeteryid = dbo.tblCemeteries.CemeteryID
WHERE (KeyNameGroupID = 1) and (dateadd(year,(age-(age*2)),dod )
between '10/10/1753' and '12/06/2004') and (dod between '10/10/1753' and
'12/06/2004')
order by getrank,forename
You simply can not use aliases in the manner in which you are trying.
"Chris Kennedy" wrote:

> I want to refere to an aliased column in an expression in another column. I
> have one called get getforename and I want to test if it is null and display
> a different value but all's I get is
> Invalid column name 'getforerank'. Regards, Chris.
> SELECT
> dbo.tblCemeteries.CemeteryName,
> Surname,
> age,
> dod,
> Forename,
> CASE WHEN tblNames.Forename='liz' THEN 0
> else(SELECT Rank FROM dbo.tblKeynames WHERE (KeyName = Forename))
> END as getforerank,
> case when getforerank is null then 1000
> else getforerank
> end,
> CASE WHEN tblNames.Surname='smith' THEN 0
> else(SELECT Rank FROM dbo.tblKeynames WHERE (KeyName = Surname))
> END as getrank FROM dbo.tblNames INNER JOIN dbo.tblCemeteries
> ON dbo.tblNames.Cemeteryid = dbo.tblCemeteries.CemeteryID
> WHERE (KeyNameGroupID = 1) and (dateadd(year,(age-(age*2)),dod )
> between '10/10/1753' and '12/06/2004') and (dod between '10/10/1753' and
> '12/06/2004')
> order by getrank,forename
>
>
|||Chris,
The SELECT list isn't materialized column-by-column from left to
right, so you can't use a name that's newly created in the select list
from elsewhere within the select list. You have two choices - either
replace getforerank with its definition, or use a nested derived table.
Since getforerank is messy, the derived table is probably an easier
solution. I've replaced your case statement by COALESCE, which should
be equivalent to what you have and less to type.
select
blah,
coalesce(getforerank, 1000),
blah
from (
-- your query except for the column using getforerank
select ...
this,
that,
messyexpression as getforerank
-- do not put coalesce(getforerank,1000) in this select
from ...
) D
Steve Kass
Drew University
Chris Kennedy wrote:

>I want to refere to an aliased column in an expression in another column. I
>have one called get getforename and I want to test if it is null and display
>a different value but all's I get is
>Invalid column name 'getforerank'. Regards, Chris.
>SELECT
>dbo.tblCemeteries.CemeteryName,
>Surname,
>age,
>dod,
>Forename,
>CASE WHEN tblNames.Forename='liz' THEN 0
>else(SELECT Rank FROM dbo.tblKeynames WHERE (KeyName = Forename))
>END as getforerank,
>case when getforerank is null then 1000
>else getforerank
>end,
>CASE WHEN tblNames.Surname='smith' THEN 0
>else(SELECT Rank FROM dbo.tblKeynames WHERE (KeyName = Surname))
>END as getrank FROM dbo.tblNames INNER JOIN dbo.tblCemeteries
>ON dbo.tblNames.Cemeteryid = dbo.tblCemeteries.CemeteryID
>WHERE (KeyNameGroupID = 1) and (dateadd(year,(age-(age*2)),dod )
>between '10/10/1753' and '12/06/2004') and (dod between '10/10/1753' and
>'12/06/2004')
>order by getrank,forename
>
>

Column Aliases

I want to refere to an aliased column in an expression in another column. I
have one called get getforename and I want to test if it is null and display
a different value but all's I get is
Invalid column name 'getforerank'. Regards, Chris.
SELECT
dbo.tblCemeteries.CemeteryName,
Surname,
age,
dod,
Forename,
CASE WHEN tblNames.Forename='liz' THEN 0
else(SELECT Rank FROM dbo.tblKeynames WHERE (KeyName = Forename))
END as getforerank,
case when getforerank is null then 1000
else getforerank
end,
CASE WHEN tblNames.Surname='smith' THEN 0
else(SELECT Rank FROM dbo.tblKeynames WHERE (KeyName = Surname))
END as getrank FROM dbo.tblNames INNER JOIN dbo.tblCemeteries
ON dbo.tblNames.Cemeteryid = dbo.tblCemeteries.CemeteryID
WHERE (KeyNameGroupID = 1) and (dateadd(year,(age-(age*2)),dod )
between '10/10/1753' and '12/06/2004') and (dod between '10/10/1753' and
'12/06/2004')
order by getrank,forenameYou simply can not use aliases in the manner in which you are trying.
"Chris Kennedy" wrote:
> I want to refere to an aliased column in an expression in another column. I
> have one called get getforename and I want to test if it is null and display
> a different value but all's I get is
> Invalid column name 'getforerank'. Regards, Chris.
> SELECT
> dbo.tblCemeteries.CemeteryName,
> Surname,
> age,
> dod,
> Forename,
> CASE WHEN tblNames.Forename='liz' THEN 0
> else(SELECT Rank FROM dbo.tblKeynames WHERE (KeyName = Forename))
> END as getforerank,
> case when getforerank is null then 1000
> else getforerank
> end,
> CASE WHEN tblNames.Surname='smith' THEN 0
> else(SELECT Rank FROM dbo.tblKeynames WHERE (KeyName = Surname))
> END as getrank FROM dbo.tblNames INNER JOIN dbo.tblCemeteries
> ON dbo.tblNames.Cemeteryid = dbo.tblCemeteries.CemeteryID
> WHERE (KeyNameGroupID = 1) and (dateadd(year,(age-(age*2)),dod )
> between '10/10/1753' and '12/06/2004') and (dod between '10/10/1753' and
> '12/06/2004')
> order by getrank,forename
>
>|||Chris,
The SELECT list isn't materialized column-by-column from left to
right, so you can't use a name that's newly created in the select list
from elsewhere within the select list. You have two choices - either
replace getforerank with its definition, or use a nested derived table.
Since getforerank is messy, the derived table is probably an easier
solution. I've replaced your case statement by COALESCE, which should
be equivalent to what you have and less to type.
select
blah,
coalesce(getforerank, 1000),
blah
from (
-- your query except for the column using getforerank
select ...
this,
that,
messyexpression as getforerank
-- do not put coalesce(getforerank,1000) in this select
from ...
) D
Steve Kass
Drew University
Chris Kennedy wrote:
>I want to refere to an aliased column in an expression in another column. I
>have one called get getforename and I want to test if it is null and display
>a different value but all's I get is
>Invalid column name 'getforerank'. Regards, Chris.
>SELECT
>dbo.tblCemeteries.CemeteryName,
>Surname,
>age,
>dod,
>Forename,
>CASE WHEN tblNames.Forename='liz' THEN 0
>else(SELECT Rank FROM dbo.tblKeynames WHERE (KeyName = Forename))
>END as getforerank,
>case when getforerank is null then 1000
>else getforerank
>end,
>CASE WHEN tblNames.Surname='smith' THEN 0
>else(SELECT Rank FROM dbo.tblKeynames WHERE (KeyName = Surname))
>END as getrank FROM dbo.tblNames INNER JOIN dbo.tblCemeteries
>ON dbo.tblNames.Cemeteryid = dbo.tblCemeteries.CemeteryID
>WHERE (KeyNameGroupID = 1) and (dateadd(year,(age-(age*2)),dod )
>between '10/10/1753' and '12/06/2004') and (dod between '10/10/1753' and
>'12/06/2004')
>order by getrank,forename
>
>

Collation Problem

HI

I am facing a problem with SqlServer 2000,

create table test123 (
[Description] [varchar] (50) COLLATE SQL_Latin1_General_Pref_CP1_CI_AS NULL
)
go
insert into test123 ( [Description] ) values ('Prve')

The insert statement works fine from SQL query Analyzer.

However when I populate the table using osql, the collation is going wrong.. I am not getting the same string in the table.

Regards

\JoeTry and this and see if it works

create table test1234 (
[Description] [varchar] (50) COLLATE Latin1_General_CI_AS
)
go
insert into test1234 ( [Description] ) values ('Prve')

That should do it.

Tell me if it works?