Predicate Or Key Word All In Uppercase?
Jul 12, 2006
Hi,
What is the dowside of not using all uppercase for predicates and key words?
I cannot find to see a problem beside adhering to a clean coding convention. After all I already have color coding so what would be the uppercase for?
Same question for the semicolon ; at the end of a sql block. Is that real necessary not to get in trouble sometime down the road or is it a non-issue. I find like I am now a C# guys if I use these ;
Just curious, I find tedious to switch from all upper case to normal case all the time. And not forget to type the ;
Thanks,
Philippe
View 7 Replies
ADVERTISEMENT
Nov 20, 2006
hi ,
what is the definition and difference between predicate and residual predicate.
give me some examples..Basically the columns used in where clause are called as predicates. Am i right.
View 1 Replies
View Related
Feb 24, 2008
i have report with parameter and he can have a null in parameter ther is null word can i change it to another word like all or any thing else
View 4 Replies
View Related
Dec 11, 2007
In sql2005 how can I update a table in my db to give me all uppercase. I have never created a sp but would like to try to do this so can someone point me in the right direction. If a select/update statement is better then please give me suggestion that way also. Thanks in advance
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1445009
MELVIN
CALVIN
A
000142718
12/11/2007 6:44:00 AM
40
503
5831
mark
lama
A
000005831
12/11/2007 7:51:00 AM
41
503
01
8300
tito
fernandez
A
000008300
12/11/6:58:00 AM
41
503
01
2380
ANNABEL
MEZA
A
00000380
12/11/2007 8:06:00 AM
41
503
01
3697
armando
hernandez
A
000003697
12/11/2007 7:24:00 AM
41
503
01
5208
juan
guardiola
A
000005208
12/11/2007 7:29:00 AM
41
503
01
523614
JOHNNY
KELLEY
A
000143625
12/11/2007 8:00:00 AM
41
503
04
View 5 Replies
View Related
Jul 23, 2005
How can I create a trigger that obliges UPPERCASE of a field in thedatabase?thanks
View 3 Replies
View Related
Mar 30, 2005
hi!
I'm just wondering does anyone know how to create an sql command that can search word by word?
what i mean is like I have a product with name 'harry potter broom'.
I want an sql command where if i type only 'harry broom' this 'harry potter broom' product will show up.
Does anyone have any idea?
Here's my sql comand: (I'm using asp.net vb script do develop this system)
-------------------------------------------------------------------------
query = "select distinct * from product where " & _
"(pname like '%" & keyword & "%' or " & _
"pdesc like '%" & keyword & "%' ) and " & _
"(price >= " & price1 & " and price <= " & price2 & _
") and status <> 'out of stock' order by price asc"
-------------------------------------------------------------------------
Thank you.
View 4 Replies
View Related
Apr 27, 2006
I have been tinkering with triggers and have tried to build one that will format the names in two columns to UPPERCASE.
Trouble is it won't run and I don't know why, how can I accomplish this uppercase task with a trigger?
View 2 Replies
View Related
Dec 30, 2004
Hi,
I have a table where the values in the Description field are all upper case. I found a script that will change all the words to UPPERCASE in the first character then LOWERCASE for the remaining until the next space is found. It will also repeat this for all words in the field.
The part I need help with is having this repeat through the entire table. Heres the code and thanks - troy
declare @input varchar(20)
declare @position int
set @input = (SELECT Description FROM tbl_ItemMast where wallysku ='10071')
set @position = 1
SET @input = Upper(substring(@input,1,1))+ LOWER(substring(@input,2,len(@input)-1))
WHILE @position < DATALENGTH(@input)
Begin
set @position = charindex(' ',@input,@position+1)
if @position = 0
goto done
SET @input = REPLACE(@input,substring(@input,@position,2),Upper(substring(@input,@position,2)))
End
DONE:
PRINT @input
View 2 Replies
View Related
Jul 23, 2005
I have a table where inactive names are lower case and active names areuppercase. Note: Not my design.Anyways I want to select all names form this table where the name isuppercase. I see collate and ASCII pop up in searches but the examplesdon't seem usable in queries as much as they were for creating tablesand such.Thanks,Phil
View 5 Replies
View Related
Aug 15, 2005
Hi all!This is the problem:User enters student name, in the database, only the 1st letter is uppercase and the rest is in Lower case.So, I want to fix this so as it is not case sensitive, i.e. the user can enter a name and it will return the recordwhether they enter it in upper or lower case.My Code:CvtUpperCase.Text = UCase(Content.Text) //stores user's input
Select Case OptionChoice
Case "Student_FirstName" MyCommand = New SqlDataAdapter("select * from [qryStudentDetails] where [qryStudentDetails].[Student_FirstName] like '" & CvtUpperCase.Text & "'" , myConnection)
....Any ideas??
View 2 Replies
View Related
Apr 9, 2008
Hi
I am trying to output the data in the table to uppercase. I am using bcp to output as text file.Any possible solution to convert the data to uppercase from the table.Any tsql code for this
Thanks in advance
View 4 Replies
View Related
Dec 10, 2007
I am always changing the Sql Query Keywords to UpperCase and formatting the T-sql.
Is there a option so that i can change the keywords to uppercase automatically in the sql 2005 management studio ?
How do most of you format the sql statements?
Please let me know it will save me and most of us like me alot of time.
Your help will be most appreciated.
Thanks in Advance
Savvy
View 6 Replies
View Related
Oct 16, 2006
In Query Editor I type statements like this:
"select * from ...."
Does Query Editor support a "macro" facility where I could, via keystorke, uppercase all t-sql keywords? (so it would look like SELECT * FROM...)
TIA,
barkingdog
P.S. You think I'm lazy? I knew a programmer who was so lazy that his password was one character long!
View 7 Replies
View Related
Jan 28, 2004
I am trying to convert all my client first and last names in my table to uppercase. They are currently listed as mixed case. Also I wanted to know what is the best way to force the data to UPPERCASE hwen a end user tries to insert or update the clients name. I am thinking about trying a trigger, but I am unsure how to set it up. Thanks for all the help.
View 4 Replies
View Related
Sep 19, 2006
I know you are able to display data all uppercase or all lowercase, but how do you display it First letter capital rest lower. Like a First or Last name?
View 3 Replies
View Related
Jan 20, 2008
I run statement down,result is "go". why goes,gone,going is not in the result?
SELECT ID, firstname, lastnameFROM [contain-1]WHERE CONTAINS(firstname, ' FORMSOF (INFLECTIONAL, go) ')
information table [contain-1]
ID firstname
1 go
2 goes
3 gone
4 going
View 2 Replies
View Related
Jan 20, 2008
what result this query?
SELECT productname
FROM product
WHERE CONTAINS(productname,'spread NEAR boysenberry' )
explain word "NEAR".
Thanks,mohsen
View 1 Replies
View Related
Jan 26, 2008
I run query down.result is null.
SELECT ID, firstname, lastname
FROM [contain-1]
WHERE CONTAINS(firstname, 'pooya NEAR yaser')
table [contains-1] information:
ID firstname
1 pooya
2 mehdi
3 peter
4 yaser
why result is null?
thanks,mohsen
View 1 Replies
View Related
Jan 30, 2008
I run query down.result is null.
SELECT ID, firstname, lastname
FROM [contain-1]
WHERE CONTAINS(firstname, 'pooya NEAR yaser')
table [contains-1] information:
ID firstname
1 pooya
2 mehdi
3 peter
4 yaser
5 pooyaandyaser
why result is null?
thanks,mohsen
View 2 Replies
View Related
Feb 1, 2008
I run query down.result is null.
Why is result null?
SELECT ID, firstnameFROM [contain-1]WHERE CONTAINS(firstname, 'yaser NEAR pooya')
table information:
ID firstname
1 ali
2 peter
3 yaser
4 yaserapooya
5 mehdi
Thanks,mohsen
View 1 Replies
View Related
Jul 20, 2005
Hi,i want to search record in a table where a varchar column is equal toa word or this word with the letter 's' or this word with the letter'e'.Today, i've this query :select * From Table Where Column = 'Word' or Column like 'Word[es]'I want to remove the 'or' of this query to have only one condition.Does someone know a solution to my problem.Excuse my poor english language.I hope you understand my problem and thks for solution.
View 2 Replies
View Related
May 10, 2007
I would like SQL Server 2000 to distinguish between uppercase and lowercase letters, but only within a single stored procedure. Also, at the end of the sp, I want the original collation to be restored. How will I implement this in my sp?
View 3 Replies
View Related
Jun 27, 2006
Hi everybody,
I have a problem using the CONTAINS predicate. I use the contains
predicate in a web page that students utilize to look for dissertations
stored in a database (MS 2000) of the library. The search is one
big textbox where students can perform any type of reaserch. For
instance, they can insert the author, the title or few keywords of a
dissertation. After they have clicked on the button Submit, the words
that they have inserted are filtered and combined (with AND) in
one string:
Example: miami, education --> 'Miami AND Education'
An example of the query that I need to perfrom is the Query Number 1:
select docnumber,Title
FROM [Log].[dbo].[DocTable1]
where contains(SearchColumn,'miami AND education') order by docnumber
SearchColumn is a field (varchar) that is the concatenation of various
fields such as Location, Title, Author, etc... that are contained in
the same table (See below for the description of the Table DocTable1).
I'm currently testing the search and I have figured out that the query described above does not work.
For example, the query should produce the same resultsof the following one, but it does not:
select docnumber
FROM [Log].[dbo].[DocTable1]
where contains(Location,'miami') AND
contains(Keywords,'education') order by docnumber
I need to implement the first query, but i can not understand why it
does not produce the same results of the second one. Any Ideas?
Thanks,
Christian
--------------------------------------------------------------
CREATE TABLE [DocTable1] (
[DocNumber] [int] NOT NULL ,
[LastName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Program] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Paper] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Location] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ANNO] [int] NULL ,
[Title] [varchar] (350) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TitleSort] [varchar] (350) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Keywords] [varchar] (350) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FT] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[URL] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SearchColumn] [varchar] (900) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_DocTable1] PRIMARY KEY CLUSTERED
(
[DocNumber]
) ON [PRIMARY] ,
CONSTRAINT [Title_Author_DocTable1] UNIQUE NONCLUSTERED
(
[Title],
[LastName],
[FirstName]
) ON [PRIMARY] ,
CONSTRAINT [FT-Constraint] CHECK NOT FOR REPLICATION ([FT] = 'Y' or [FT] = 'N')
) ON [PRIMARY]
GO
View 5 Replies
View Related
Jan 4, 2007
I am having problems with the following SQL statement. It works how I want it to work if I use = @SearchQuery but when I try to switch to LIKE I run into problems. I tried it the way it is shown below and I also tried LIKE '%'@SearchQuery'%' with no luck. Does this have something to do with the pattern to match being a parameter? How would I go about doing this correctly.
"SELECT server_info.*, network_adapter.* FROM server_info LEFT JOIN network_adapter " + "ON server_info.server_id = network_adapter.server_id WHERE " + DropDownList1.SelectedValue.ToString() + " LIKE '%@SearchQuery%'"
Thanks
View 2 Replies
View Related
Oct 3, 2007
Anybody know how to do this?
"Select something from some table where id in (@IDlist)"
where @IDlist is a comma-separated list of values (or something top that effect)
View 1 Replies
View Related
Jul 23, 2005
HiI am having a debate with one of the Postgres developers (Tom Lane)according to him ms-sql does not implement predicatelocking(SERIALIZABLE).Where predicate locking is defined as (from the postgres help):"12.2.2.1. Serializable Isolation versus True Serializability"It states: "To guarantee true mathematical serializability, it isnecessary for a database system to enforce predicate locking, whichmeans that a transaction cannot insert or modify a row that would havematched the WHERE condition of a query in another concurrenttransaction"Now I put it to him that that is exactly what SERIALIZABLE does isMS-SQL.But his response was "Only for WHERE conditions that can be expressedas a simple range constraint."Now is he correct i.e SERIALIZABLE works "Only for WHERE conditionsthat can be expressed as a simple range constraint." ?Regards,Daniel RothMCSD.NET
View 8 Replies
View Related
Apr 21, 2008
am encountering the NO JOIN PREDICATE warning in a query plan for a particualr SQL statement. In poking around, I've come across psotings that mention the CU 4 for SQL Server 2005. The update applies to a KB article that addresses complex queries with an outer join.
But in my case, it's not an outer join causeing the problem - they are all left joins. Here is the piece of SQL that is running extremely slowly:
Code Snippet
UPDATE @TESTTABLE
SET [@TESTTABLE].SignInCancelledNoEMR =
(SELECT q.cnt FROM
(SELECT COUNT(*) AS cnt,cl.Clinic_id AS ClinicIDq,cl.CLINIC_REG_ID AS MarketIDq
FROM dbo.tblactivity AS t
LEFT JOIN [ERIIProduction].dbo.computer c on t.machineid = c.comp_windowsname
LEFT JOIN [ERIIProduction].dbo.clinic cl on cl.clinic_id = c.comp_clinic_id
LEFT JOIN [ERIIProduction].dbo.Region r ON cl.CLINIC_REG_ID = r.REG_ID
WHERE t.status = '1'
AND cast(checkindate as datetime) >= @startDate
AND cast(checkindate as datetime) < DATEADD(d, 1, @endDate)
AND cl.clinic_reg_id IN (SELECT Item FROM dbo.Split(@regionIDList,','))
AND NOT EXISTS (SELECT 1 FROM [ERIIProduction].dbo.VisitPatient vp WHERE (t.activityid = vp.VSPAT_GalvanonActivityID)) --AND (t.memberid = vp.VSPAT_GalvanonMemberID))
GROUP BY cl.Clinic_id,cl.CLINIC_REG_ID) AS q
WHERE q.ClinicIDq = ClinicID AND q.MarketIDq = MarketID)
Before I apply the CU and enable the trace flags, I'd like to know if there is something else I can do within the query itself to make it run faster. I've tried the OPTION(FORCE ORDER ) hint, but that doesn't have any effect on the queery run time.
And as a whole, the query cost relative to the entire batch jumped from 75% to 96%.
Granted the execution time of the entire batch dropped from 9 minutes to 1 minute, this query should not even take that long.
Thanks for any input...!
View 6 Replies
View Related
Aug 16, 2007
Hi guys,
I need to change some characters when reading the values of a certain columns, I am trying to use something like the code below, but the following error:
"Cannot use a CONTAINS or FREETEXT predicate on table 'JURNALTRANS' because it is not full-text indexed."
Code Snippet
CASE
WHEN Contains (JurnalTrans.DESCRIPTION, 'A') THEN Replace(JurnalTrans.DESCRIPTION,'A','A1')
WHEN Contains (JurnalTrans.DESCRIPTION, 'B') THEN Replace(JurnalTrans.DESCRIPTION,'B','B2')
ELSE JurnalTrans.DESCRIPTION
END AS 'Cost Description',
I am also trying something like this
Code SnippetSELECT DESCRIPTION AS 'X' FROM JURNALTRANS WHERE CONTAINS(DESCRIPTION, 'Y')
but getting the same error
"Cannot use a CONTAINS or FREETEXT predicate on table 'JURNALTRANS' because it is not full-text indexed."
Any help will be appreciated,
Thanks in advance,
Aldo.
View 4 Replies
View Related
Aug 1, 2015
The states in this report are all in caps TEXAS, CALIFORNIA, etc.. Is there a way to use expressions to only have the first letter in uppercase and rest in lowercase?
View 3 Replies
View Related
Sep 24, 2007
Is the word "Name" a reserved word in SQL? look at line 10 of my stored procedure. When I use the word "Name"it is highlited in blue by SQL Server?
Note I only list part of the stored proc
1 CREATE PROCEDURE [dbo].[GetXMLPeopleNames] 23 (4 @Status nvarchar(3)5)6 AS7 SELECT8 PersonId,9 PersonDescription,10 Name,11 UpdateDate,12 UpdateAppUser13 FROM14 Customer WHERE Customer.PersonDescription=@Status15 ORDER BY
View 2 Replies
View Related
Apr 14, 2004
I have a report in SQL that passes parameters at runtime entered by the user for two date ranges (beginning and ending). I'm trying to write a formula that will print a specific field *only if* the specified date range entered by the user is BETWEEN a specific value (like 200401). This is kind of reverse of a normal WHERE, BETWEEN clause.
I tried a standard BETWEEN predicate in my WHERE clause like:
IF '200401' BETWEEN ?BegPer and ?EndPer then salesanal.ptdbud01 else 0
But, it's returning an error that my Then statement is missing. I can't use a normal statement like 'IF ?BegPer >= '200401' and ?EndPer <= '200401', then.....' because users could enter a RANGE of periods, so it would be difficult to code all of the possible combinations this way. I'm actually doing this in Crystal, but if someone can give me a standard MSSQL example, I can translate that over to Crystal.
Thanks in advance,
Michelle
View 4 Replies
View Related
Oct 8, 2015
I have two tables. Table A has 2162 rows and table B has 101 rows. There isn't any join keys that I can join on these two tables. But I need everything from table B and only one column (Col1) from table A. It will result in a cross join and retruns 218362 (2162 * 101) rows, which is correct. But this takes about 30 seconds to complete. Any workaround to accomplish this? Optimizer shows no predicate joins on the Nested Loop and to me it is correct.
Here is what I have so far.
SELECT B.*, A.col1
FROM TableB B
LEFT JOIN TABLE A A
ON 1 = 1
View 9 Replies
View Related
Jul 20, 2005
I have been searching for an escape character or a way of escapingdouble quotes that are actually in a string that I am using in thecontains predicate.Here is an exampleselect *from tablewhere contains(field, '"he said "what is wrong", that is what hesaid"')I need the double quotes in the string because they are part of thetext. Of course, Fulltext search raises the errorServer: Msg 7631, Level 15, State 1, Line 1Syntax error occurred near 'what is wrong", that is what he said'.Expected ''''' in search condition '"he said "what is wrong", that iswhat he said"'.If I remove the double quotes, the search does not return the properresults.Thanks in advance for the helpBill
View 2 Replies
View Related
