Dear Group
Just wondered how I can avoid the CHAR(32) to be inserted if @String1 is NULL?
SET @String3 = ISNULL(@String1,'') + CHAR(32) + ISNULL(@String2,'')
Thanks very much for your expertise and efforts!
Best Regards,
Martin
Hey, I'm taking an intro SQL Server class, and I have a pretty simple homework assignment. We were provided with a DB and asked to write several SELECT statements. However, I'm stuck up one of the questions. Here is the question: 12.Create a SELECT statement that displays all employees and their Qualifications. Display that individuals with no Qualifications as having ‘NoQual’. Hint: Use a function to determine this ‘empty’ field using ISNULL.
Here is what I have:
SELECT
FNAME + ' ' + LNAME AS 'Employee Name', ISNULL(QUALID, 'NoQual') AS 'Qualifications'
FROM
EMPLOYEE, QUALIFICATION
WHERE
EMPLOYEE.QUALID = QUALIFICATION.QUALID;
However, I do not get any results that have a NULL value in the QUALID column. Here is the code for the DB:
CREATE TABLE emplevel
(LevelNoint,
LowSalaryint,
HighSalaryint,
CONSTRAINT emplevel_levelno_pk PRIMARY KEY (LevelNo));
GO
CREATE TABLE position
(PositionIdint,
PosDescVARCHAR (10),
CONSTRAINT position_positionid_pk PRIMARY KEY (PositionId));
GO
CREATE TABLE qualification
(QualIdint,
QualDescVARCHAR (11),
CONSTRAINT qualification_qualid_pk PRIMARY KEY (QualId)
);
GO
CREATE TABLE dept
(DeptIdint,
DeptNameVARCHAR (12) ,
LocationVARCHAR (15),
EmployeeIdint,
CONSTRAINT dept_deptid_pk PRIMARY KEY (DeptId)
);
GO
CREATE TABLE employee
(EmployeeId int,
LnameVARCHAR (15) CONSTRAINT employee_lname_nn NOT NULL,
Fname VARCHAR (15) CONSTRAINT employee_fname_nn NOT NULL,
PositionId int,
Supervisorint,
HireDate DATETIME,
Salaryint,
Commissionint,
DeptIdint,
QualIdint,
CONSTRAINT employee_employeeid_pk
PRIMARY KEY (EmployeeId)
);
GO
CREATE TABLE dependent
(EmployeeId int,
DependentIdint,
DepDOBDATETIME,
RelationVARCHAR (8),
CONSTRAINT dependent_empiddepid_pk PRIMARY KEY (EmployeeId, DependentId)
);
GO
INSERT INTO position VALUES (1, 'President');
INSERT INTO position VALUES (2, 'Manager');
INSERT INTO position VALUES (3, 'Programmer');
INSERT INTO position VALUES (4, 'Accountant');
INSERT INTO position VALUES (5, 'Salesman');
INSERT INTO emplevel VALUES (1, 1, 25000);
INSERT INTO emplevel VALUES (2, 25001, 50000);
INSERT INTO emplevel VALUES (3, 50001, 100000);
INSERT INTO emplevel VALUES (4, 100001, 500000);
INSERT INTO qualification VALUES (1, 'Doctorate');
INSERT INTO qualification VALUES (2, 'Masters');
INSERT INTO qualification VALUES (3, 'Bachelors');
INSERT INTO qualification VALUES (4, 'Associates');
INSERT INTO qualification VALUES (5, 'High School');
INSERT INTO dept VALUES (10, 'Finance', 'Charlotte', 123);
INSERT INTO dept VALUES (20, 'InfoSys', 'New York', 543);
INSERT INTO dept VALUES (30, 'Sales', 'Woodbridge', 135);
INSERT INTO dept VALUES (40, 'Marketing', 'Los Angeles', 246);
INSERT INTO employee VALUES (111, 'Smith', 'John', 1, NULL,'04/15/1960', 265000, 35000, 10, 1);
INSERT INTO employee VALUES (246, 'Houston', 'Larry', 2, 111,'05/19/1967', 150000, 10000, 40, 2);
INSERT INTO employee VALUES (123, 'Roberts', 'Sandi', 2, 111,'12/02/1991',75000, NULL, 10, 2);
INSERT INTO employee VALUES (433, 'McCall', 'Alex', 3, 543,'05/10/1997',66500, NULL, 20, 4);
INSERT INTO employee VALUES (543, 'Dev', 'Derek', 2, 111,'03/15/1995',80000, 20000, 20, 1);
INSERT INTO employee VALUES (200, 'Shaw', 'Jinku', 5, 135,'01/03/00',24500, 3000, 30, NULL);
INSERT INTO employee VALUES (135, 'Garner', 'Stanley', 2, 111,'02/29/1996',45000, 5000, 30, 5);
INSERT INTO employee VALUES (222, 'Chen', 'Sunny', 4, 123,'08/15/1999',35000, NULL, 10, 3);
INSERT INTO dependent VALUES (543, 1,'09/28/1958','Spouse');
INSERT INTO dependent VALUES (543, 2,'10/14/1988','Son');
INSERT INTO dependent VALUES (200, 1,'06/10/1976','Spouse');
INSERT INTO dependent VALUES (222, 1,'02/04/1975','Spouse');
INSERT INTO dependent VALUES (222, 2,'08/23/1997','Son');
INSERT INTO dependent VALUES (222, 3,'07/10/1999','Daughter');
INSERT INTO dependent VALUES (111, 1,'12/12/1945','Spouse');
ALTER TABLE dept
ADD CONSTRAINT dept_employeeid_fk FOREIGN KEY(EmployeeId)
REFERENCES employee(EmployeeId);
GO
--ALTER TABLE employee
--ADD CONSTRAINT employee_supervisor_fk FOREIGN KEY(Supervisor)
--REFERENCES employee(EmployeeId);
ALTER TABLE employee ADD CONSTRAINT employee_positionid_fk FOREIGN KEY (PositionId)
REFERENCES position (PositionId);
GO
ALTER TABLE employee ADD CONSTRAINT employee_deptid_fk FOREIGN KEY (DeptId)
REFERENCES dept (DeptId);
GO
ALTER TABLE employee ADD CONSTRAINT employee_qualid_fk FOREIGN KEY (QualId)
REFERENCES qualification (QualId);
GO
ALTER TABLE dependent ADD CONSTRAINT dependent_employeeid_fk FOREIGN KEY (EmployeeId)
REFERENCES employee (EmployeeId);
GO
okay, using isnull function we could replace null value..
but i want to do opposite, i want to replace if it's NOT null..
i tried notisnull also cannot..
Note : this is for select statement
SELECT isnull(d.ClientID,'-') FROM blabla
How to replace something if it's not null
SELECT isNOTnull(d.ClientID, '-')
SELECT JS_ID = ISNULL(ID.JS_ID,'-')
FROM
dbo.FM_INVOICE I,
dbo.FM_INVOICE_DETAILS ID
WHERE
I.INVOICE_ID = ID.INVOICE_ID
AND WO_ID = '-'--ISNULL(@GetLatest, '-')
is there any mistake i made? because it still return NULL when no data is found. how do i make it return '-' if a null is found
I'm constructing a single string of several counts with concatenated labels using SQL and want to not show zeros (or their labels). Is there a function within an SQL statement that will let me do this? ISNULL() sort of does this, but I really need to test for zero instead of NULL to eliminate noise data from the string.
View 2 Replies View RelatedWhat is wrong with this code. I can not get ISNULL function to work
IF ISNULL([JRL_Req].[MDN])
UPDATE JRL_Req
SET [JRL_Req].[Product Code] = [OrderItem].[ProductCode]
FROM JRL_Req
INNER JOIN [order]
ON [JRL_Req].[Order ID]= [order].[OrderId]
INNER JOIN [OrderItem]
ON [order].[OrderId] = [OrderItem].[OrderId]
ELSE
UPDATE JRL_Req
SET [JRL_Req].[Product Code] = [OrderItem].[ProductCode]
FROM JRL_Req
INNER JOIN [order]
ON [JRL_Req].[Order ID]= [order].[OrderId]
INNER JOIN [OrderItem]
ON [order].[OrderId] = [OrderItem].[OrderId] AND [OrderItem].[MDN] = [JRL_Req].[MDN]
END IF
Hi,
I created a new column in Derived column editor wich depends on two columns that I'm evaluation.
This is my function for the new column:
(COLUMN1 == "Hello") || ISNULL(COLUMN1) ? COLUMN2 : COLUMN3
The problem is that when a NULL data comes for COLUMN1, it doesn't insert the value of COLUMN2 as the formula estipulates. It looks like is not understanding de ISNULL function.
Any suggestion?
Thanks in advance.
i'm trying to run an append query using data from 2 tables. i want to replace nulls with blanks ('') bellow is my statment. when i run this statment with out the iif(isnull)) statmentes the query works fine. is there another way of replacing my nulls with blanks.
Thank you,
Thomas
insert into tblcustomers (cusName, cusNumber, Active, cusContact, cusCrLimit,cusTerms)
SELECT dbo.tblCustomersIOA.CustomerName, dbo.tblCustomersIOA.Cust#,
dbo.tblCustomersIOA.Active,
iif(isnull(dbo.tblCustomersIOA.Contact),'',dbo.tbl CustomersIOA.Contact) ,
dbo.tblCustomersIOA.CreditLimit,
FROM dbo.tblCustomerNotesIOA RIGHT OUTER JOIN
dbo.tblCustomersIOA LEFT OUTER JOIN
WHERE (dbo.tblCustomersIOA.CountryID = 1) AND (dbo.tblCustomersIOA.StateID = 2);
Hello all,
I have a problem that some of the data I reciave from the DB is null.
I would like to replace it with '0'.
I used the function IsNull on the SQL statment.
The problem is that I recaived a false/true values..
Is there an option to get the real data?
this is the SQL:
SELECT OrderDetails.itemID, OrderDetails.itemName, OrderDetails.quantityToPick, OrderDetails.quantityPicked, OrderDetails.colorDescription,
OrderDetails.colorDetails, OrderDetails.bases, OrderDetails.diameter, OrderDetails.axis, OrderDetails.referenceNum, OrderDetails.remarks,
Isnull(Stock.stockQuantity, 0) AS stockQuantity, OrderDetails.quantityOrdered, OrderDetails.status
FROM OrderDetails LEFT OUTER JOIN
Stock ON OrderDetails.itemID = Stock.itemID
WHERE (OrderDetails.storeID = @storeIDTemp)
Any ideas?
I have an MDX query
WITH MEMBER [Measures].[Parm1] AS
[Member].[Member Deceased Year].currentmember.member_caption MEMBER [Measures].[ResultValue]
AS ([Month].[Calendar].[Month].&[20150531], [Measures].[Member Count])
SELECT ({[Measures].[Parm1],[Measures].[ResultValue]}) ON COLUMNS,
ORDER([Member].[Member Deceased Year].[Member Deceased Year], [Measures].[Parm1], DESC) ON ROWS
FROM [TXERSDW]
Now from the Above query I am resulting with NULL as Parm1 values, I need to replace with a value of 12-31-9999 in the Parm1 if there are NULLS.
Hi,
Why does this result produce 'Null' and not the expected string of 10 B's?
The var @SecurityString is a VarChar Type.
WHILE @LoopCount <= 10
BEGIN
SET @SecurityString = @SecurityString + 'B'
SET @LoopCount = @LoopCount + 1
END
SELECT @SecurityString AS SecurityCode
Any pointers would be a great help, thanks.
create procedure ChangePassword(@sUser char(20),@sPassword char(20))
as
begin
execute immediate 'GRANT CONNECT TO ' + @sUser + ' IDENTIFIED BY ' + @sPassword
grant execute on ChangePassword to public
end
I m getting syntax error at '+' sign. I saw in BOL and it is exactly the same.
Can nyone help me out?
Thx
create procedure CheckSQLErrors( @TheCode integer, @TheState integer, @Routine varchar(40), @Help varchar(40))
as
begin
{ call LogMsg('SQLA',@Routine,@Help,'sqlstate=' + @TheState +
', sqlcode=' + @TheCode) }
end
I m getting this error.
"Incorrect syntax near + "
+ is used for string concatenation. I tried to use CAST to convert
@TheState and @TheCode variables to varchar but did not work. Can you help me out?
FYI
LogMsg is a sproc
create procedure
dbo.LogMsg( @aAppName varchar(18), @aRoutine varchar(20), @aType varchar(5), @aMsg varchar(255))
as
begin
insert into MessageLog(strAppName,strRoutineName,strType,strMe ssage) values(
@aAppName,@aRoutine,@aType,@aMsg)
end
GO
Thks
I'm trying to concatenate 3 columns into 1. Can someone provide me w/ the syntax?? :) thx
View 1 Replies View RelatedI have the following data:
create table TempTable(name varchar(50), value varchar(50))
insert into temptable values ('A', 'one')
insert into temptable values ('A', 'two')
insert into temptable values ('A', 'three')
insert into temptable values ('B', 'four')
insert into temptable values ('B', 'five')
and i would like the following output:
'A', 'one, two, three'
'B', 'four, five'
any ideas on how to accomplish this in Sql Server 2000?
thx in advance..
I want to concatanate all text rows returned by the following sql statement:
"SELECT Text FROM PageText WHERE PageTextId = 1"
Table "PageText" has the following columns:
---------------------------------------------
PageTextId (int)
SortOrder (int)
Text (nvarchar(4000))
Is it possible to do this? Kinda like doing a "SUM()" if the values would have been numeric?
Hi all,
I have a sitaution here where I need to convert some relational data to a flat file. I have a primary record that flatens out pretty well with the exception of two columns that need to have row data converted to strings via concatenation. The column size is Char(146) . I attempted to use 2 cursors to create the strings.
C1 --outside cursor to pull unique record id (161,000+ records)
C2 -- SELECTs the top 29 secondary (relational) records for each C1 rec
(FIELDX as Char(5))
FIELDX is the concatenated up to 29 times and inserted in to a flat table
based on record id for flat file export.
The issue is that this takes FOREVER to run and the 3Ghz XEON w/2GB Ram server weeps.
Declarations are as follows:
DECLARE C1 CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT Distinct Record_ID
FROM tblProcedure
DECLARE C2 CURSOR FAST_FORWARD READ_ONLY
FOR
--need only the top 29 relational records to string out
SELECT TOP 29 Cast(pr_icd1 as Char(5))
FROM tblProcedure WHERE Record_ID = @RECID --From C1
OPEN C2
SET @tmpICDstr = ''
FETCH NEXT FROM C2 INTO @tmpICDchar
WHILE @@FETCH_STATUS = 0
BEGIN
SET @tmpICDstr = @tmpICDchar + @tmpICDstr
FETCH NEXT FROM C2 INTO @tmpICDchar
END
--'INSERT INTO [Validation].[dbo].[tmpICDStr] (RECID, sg) VALUES
(@RECID, @tmpICDstr)
--'INSERT INTO @tmp (RECID, STRsg) VALUES (@RECID, @tmpICDstr)
SET @tmpICDchar = ''
Anybody have a suggestion on how to speed this up. I am looking at about 1min/100 C1 records. Do the math for 161,000+ C1 records. Ugh.
Any suggestion would be appeciated!
tnx
Hi,How can I remove a part of string from complete string in SQL?this is something i want to do:update aceset PICTURE = PICTURE - '\SHWETABHShwetabhI'But here ' - ' is not allowed.How can I remove the string \SHWETABHShwetabhI from the columnPICTURE?Regards,Shwetabh
View 5 Replies View RelatedI'm attempting to use the isnull function to convert null values in a column to a blank. It works on the alpha field but not the numeric and I was wondering what can be done for numeric field conversion.
View 6 Replies View RelatedI guess I'm the only one with this problem -- couldn't find anything on it in the back questions. Maybe it's a weird problem. :)
Anyway, although I'm not new to SQL, I am a bit new to stored procedures, and MS SQL Server 7. (I've been using mySQL, decent, but doesn't have many features ... )
I used some ASP and stored procedure code from 4guysfromrolla.com for session tracking through SQL Server.
I've modified most of the stored procedures so that they actually work. :)
The tables it uses are simple:
sessions: sessionid (uniqueidentifier), date_stamp (datetime), sessionipaddr(varchar(50))
sessionvalues: sessionid (uniqueidentifier), sessionvalname (varchar(100)), sessionvaldata (varchar(8000))
To answer some questions before they're asked: It's a resume database, and does need to be able to store 8000 characters at a shot. (I'm hoping 8000 is as large as it gets for this particular field.)
There's only one problem now: One of the stored procedures enters information into the sessionvalue field of the table. However, much of our data contains apostrophes ('), and we need to be able to store them. I thought that modifying the execute statement would do it, something like:
EXECUTE sessiondata '{EC8131F6-409A-11D4-8E88-00A0C9E4F36E}', 'ExpWorkDescs', 'Here' + CHAR(39) + "s some data"
This doesn't work. Indeed, even if the concatenation worked, CHAR(39) doesn't in this context.
Then I thought I'd be really clever, and try a trick from mySQL:
EXECUTE sessiondata '{EC8131F6-409A-11D4-8E88-00A0C9E4F36E}', 'ExpWorkDescs', 'Here's some data'
Naturally, that one didn't work, either. (That was a long shot, admittedly!)
This is mission-critical. Not only apostrophes, but quotes and other punctuation marks must be able to be transferred. Anyone know a way to do it?
I have 8 fields - I have requirement to concatenate using '+' operator with semicolon delimiter but issues is in the
Output I get semicolons for the fields that are empty below is my code :
-------------
case
when [SLII Request Type] ='Job Posting' and [SmartLaborII Request Status] like 'Pending Approval (Level 4%'
and [New Extension or Replacement Audit Flag] like 'FLAG%'
then 'Reject – New, Extension, Replacement invalid entry' --'it is jp'
else ''
end as [ES Fully approved data 1],
case
[Code] ....
I have a need to create a table in a sql server database from C# code. The kicker is that the user must be able to specify the table and field names via the UI. I can do a bit of sanity checking but as long as they enter something reasonable I need to accept it. Normaly I always ADO parameters to sanitise any user parameters but they can't be applied to table and field names, only values. As far as I'm aware that leaves me needing to concatenate strings and that's something I usually avoid like the plague due to risk of SQL injection.
My actual question : Assuming string concatenation is my only way forward, how can I sanitise the values that would go into the table name and fieldname bits of a CREATE TABLE statement to ensure that injection can't occur? I've been pondering it and I think I just need to check for semi-colons. Without a semi-colon I don't think a user could inject an extra statement could they?
Hi,I'm trying to concatenate a Description (nchar(100)) and Date(datetime) as Description and my initial effort was just"...description+' '+open_date as description..." which throws a date/string conversion error; finally came up with a working string belowbut don't think it's the optimal way to do this - any suggestions?select (rtrim(description)+''+rtrim(convert(char(2),datepart(mm,open_date)))+'/'+convert(char(2),datepart(dd,open_date))+'/'+convert(char(4),datepart(yyyy,open_date))) as description fromoncd_opportunity where opportunity_id=?open_date is not a required field at the db level, but it is requiredon the form so it should not be null as a rule.
View 2 Replies View RelatedHi!
Is there any fucntion avaible in SQL for search a character position in a string.
e.g. 'ABC-DEF'
- is located at the position of 4
I tried the charIndex, Is it same as Instr in ORACLE. Is there any INSTR function available
does anyone know a function to count the number of times a substring appears in a string?
example NumberOfTimes("i","mississippi") would return 4
I need to take the character from a text field. I need the character which
is the second one from the end(right). Like out of '12345' or '99821'
I would need the 4 from the first and the 2 from the second. Can I combine
a string and a right statement to do this?
Thanks
Here is the scenario.
I have a 10 position varchar field that holds a project number. The project number is in the form yy-nnn... where yy is the current year (06) and nnn is a sequential number. The project number is displayed on a data entry form so that the next record has the next sequential number.
So when the data entry form first opens the project number field has 06-1 after that record is entered the form is cleared and reinitialized and the project number now shows 06-2. If the form is closed and then reopened the project number would still show 06-2 if that previous record was not entered and 06-3 if it was.
The way I am going about getting the next sequential number is the following:
SELECT MAX(RIGHT(ERFNumber, LEN(ERFNumber)-3)) AS MaxERFNumber From Table1
This approach is working fantastic until record 06-9 is entered. The above line does determine that 06-9 is the MAX record and then creates a record 06-10 but every subsequent time a record is attempted it still thinks 06-9 is the MAX value of the above statement.
I am assuming that since the field is defined as a varchar the MAX function is applying character logic versus numeric logic thus the 9 being treated as the MAX value.
Is there an SQL function that can change the result of RIGHT(ERFNumber, LEN(ERFNumber)-3) to a numeric value so that the MAX function will work correctly.
I know there are many other ways to do this but in the interest of time because the application is almost finished, changing this now is going to be a major undertaking.
Any help or guidence would be greatly appreciated.
Hi all, iam trying to find a string function which would replace column value where there is a ssn with 1 and anything else(blank,null,...) with 0. i need to count the number of rows with ssn and one without ssn.
i checked few string functions but no use
any help appreciated
hi kristen or anybody there,
is there a way you can concat string columns in a select statement... i have two tables (joined) and i wanted to display a concatenated values.
data details:
table 1
pk_transactionid transactiondate and so on
1 1/1/2007 *********
2 1/2/2007 *********
3 1/3/2007 *********
table 2
pk_rowid fk_transactionid description
1 1 record1
2 2 record2
3 3 record3
4 1 record4
5 2 record5
6 1 record6
result set should be like this
pk_transactionid transactiondate description --> this is concatenated
1 1/1/2007 record1, record4, record6
2 1/2/2007 record2, record5
3 1/3/2007 record 6
thanks
SlayerS_`BoxeR` + [ReD]NaDa
select distinct(f.agency+y.year+s.type) as SDNFIn this select clause, I try to use the last 2 digit of year for y.yearvalue. which function should i apply to it?
View 1 Replies View RelatedIs there a way to convert special characters such ö to o?
Cheers
I have a table with a column called Userdef. I is a user defined field. It looks like this ;;Polk;D-0002;;;;As you can see it is delimited by semicolons. I need to separate the semicolons into separate files like this Field1Field2Field2Field4Field5Field6<null><null>Polk<null>D-0002<null>How do I write this query in SQL Server?
View 1 Replies View RelatedThis function is useful for fixed width output. E.g.
String(expr,10-len(expr),'0') will give you a number with leading zeros so it is always 10 characters long.
Thanks in advance for your time.