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!
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
Under sql 2000 I could connect to a server as a speciifc sql user. How can I emulate that under sql 2005 Query Editor. (It doesn't appear to let me define the user to log in as.)
TIA,
Barkingdog
Not exactly a TSQL question. Query Editor in Management Studio keeps reformatting my sql code every time I hit save, making the code much harder to read. Is there a way to preserve line breaks and indentation in the SQL when creating a view? Thanks.
View 1 Replies View RelatedI have an MDX query that is running fine in SQL Mgmt Studio, but crapping out in the MDX editor in SSRS. How do I resolve this?
My query & partial results are given below:
SELECT NON EMPTY {
[Product].[Category].[All],
[Product].[Category].&[1],
[Product].[Category].&[2],
[Product].[Category].&[3],
[Product].[Category].&[4] } *
{ [Measures].[Sold Amt USD], [Measures].[Sold Qty] } * { [Time].[Year].&[2007], [Time].[Year].&[2006] }
ON COLUMNS,
NON EMPTY {
[Customers].[Customers].[Cust Group].&[Specialty].&[100001291 Customer Name] }
*
[Customers].[Area].[All].CHILDREN * [Time].[Month Full Name].[All].CHILDREN
ON ROWS
FROM [Sales And Margin Cube]
WHERE ( [Customers].[Customer Group].&[Specialty] )
All
All
All
All
Prod1
Prod1
Prod1
Prod1
Prod2
Prod2
Prod2
Prod2
Prod3
Prod3
Prod3
Prod3
Prod4
Prod4
Prod4
Prod4
Sold Amt USD
Sold Amt USD
Sold Qty
Sold Qty
Sold Amt USD
Sold Amt USD
Sold Qty
Sold Qty
Sold Amt USD
Sold Amt USD
Sold Qty
Sold Qty
Sold Amt USD
Sold Amt USD
Sold Qty
Sold Qty
Sold Amt USD
Sold Amt USD
Sold Qty
Sold Qty
2007
2006
2007
2006
2007
2006
2007
2006
2007
2006
2007
2006
2007
2006
2007
2006
2007
2006
2007
2006
100001291 Customer Name
Region 1
1-Jan
419,627
376,299
1,418
1,665
202,032
291,389
374
632
1,737
692
2
1
17,181
18,019
18
24
130,131
906
194
1
100001291 Customer Name
Region 1
2-Feb
905,706
411,105
3,062
2,011
497,540
258,067
815
519
12,171
(null)
12
(null)
30,432
10,479
32
13
223,008
25,653
339
35
100001291 Customer Name
Region 1
3-Mar
982,528
763,321
2,826
2,062
384,939
329,926
675
605
5,327
769
8
1
9,797
27,056
9
33
497,903
335,283
740
519
Thank you,
NL
I have a sample photo database where we have added keywords to search for photos. I wanted a way to list all of the keywords that are in the database individually. The problem is in my keyword field there are many keywords seperated by a comma.
Ex: "bull, barrel, rodeo, western, cowboy" would in the keyword field for one photo.
I wanted to select distinct all of the individual words from each keyword field in all of the records.
Can this be done? What would the query look like?
I am looking for a list like:
bull
barrel
rodeo
western
cowboy
Any suggestions?
Thanks,
Rob
In query editor I displayed a single row from a table. The row is so long that I need to scroll horizontally back and forth to check out it's fields. Using t-sql (or otherwise) can I display the row like this: (vertically)
Field Name 1: < data value 1>
Field Name 2: < data value 2>
Field Name 3: < data value 3>
Field Name 4: < data value 4>
etc.
TIA,
barkingdog
Hi all,I have two tables:workgroups (wg_id, wg_name)workgroups_keywords (wgk_wg_id, wgk_keyword)Each workgroup has an associated list of one or more keywords.What I want do to at first was given a particular list of keywordsbring back a list of workgroups that have at least one matching keywordassociated with it.I have the following query:select distinct(wg_id), wg_namefrom workgroups, workgroups_keywordswherewgk_keyword in (#QuotedValueList(Keywords.wgk_keyword)#)andwg_id = wgk_wg_idorder by wg_nameThis works great.However, is there a way in a single query to order the returned rows bythe number of keywords that are found to be matching (in other words anorder by relevancy, the more keywords that match the more relevant thereturned row)?Thanks in advance.David
View 2 Replies View Relatedhey guys
i have a query that takes too long that, actually i never got it to finish excuting
the query looks like tht
select referer,count(*) from t1 where referer is not null
and referer in( select distinct(Referer_Direct) from t2 where Referer_Direct is not null )group by referer
the inner select just returns 5 rows
so when i replace the inner select with actual values like tht
select referer,count(*) from t1 where referer is not null
and referer in('val1','val2','val3','val4','val5' )group by referer
it excutes immediatly
any clues how to solve this issue
the db is running on sql server 2005 express SP2
thx in advance
Can i extend the "Query Builder" dialog of OLEDB Source Editor for developing custom source component?
i am having a problem querying a field in a database to show all records where the title has a keyword within the title.
Select * FROM tblCourse
WHERE title =@Search
But not the full title just a keyword within the field?
Thanks
hi
I am having trouble with the following query within my store procedure.
as you can see, i am making an union of 2 separate queries.
in the 2nd part of the union, i encounter a column in the database where the column name is the same as the keyword "desc"
is there a way which i can get around this, or is there any other way that i can sepecify the column? (excluding the possibility of using *)
CREATE PROCEDURE topcat.getTransHistory
(
@contact_id numeric(9)
)
AS
BEGIN
DECLARE @phone_no varchar(255)
set @phone_no = (select top 1 phone_num from topcat.class_contact where _id = @contact_id)
select cast(trans_new.trans_date as varchar(50)) date,
'' code,
cast(payment.date_paid as varchar(50)) datepaid,
'' "desc",
case payment.payment_type
when 'cheque' then trans_new.item_total
else ''
end pledged,
'' mail,
case payment.payment_type
when 'cheque' then ''
else trans_new.item_total
end received,
'' receipt
from topcat.class_transaction trans_new left outer join topcat.class_payment payment on trans_new._id = payment.transaction_id
where trans_new.contact_id = @contact_id
union
select cast(trans_old.date as varchar(50)) "date",
trans_old.code,
cast(trans_old.datepaid as varchar(50)) "datepaid",
trans_old.desc,
cast(trans_old.pledged as varchar(128)),
trans_old.mail,
cast(trans_old.received as varchar(128)),
trans_old.receipt
from topcat.MMTRANS$ trans_old
where phone = @phone_no
END
GO
Cheers
James :)
If the data type of field is "varchar",we can use "like" to query if it hassome substring.Such as "where custom.valuevariant like '%Verizon%' ", it will query out allrecords that contains string "Verizon".But how to do when data type of field custom.valuevariant is "image"?Thanks
View 2 Replies View RelatedCREATE TABLE BILL_DETAIL
([objid] int,[x_billable_to] varchar(19), [x_bill_quantity] int,
   [x_billable_yn] int, [x_bill_rate] int,  [COST_TYPE] varchar(19) )
INSERT INTO BILL_DETAIL
([objid], [x_billable_to], [x_bill_quantity], [x_billable_yn], [x_bill_rate],[COST_TYPE])
[code]...
how to get records using stuff keyword as above i want to query using where condition with where objid=1 and should frame output as Parking, Toll only 1 input parameter need to given.
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
How can I create a trigger that obliges UPPERCASE of a field in thedatabase?thanks
View 3 Replies View RelatedI 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?
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 RelatedHi,
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
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??
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
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
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 RelatedI 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 RelatedI 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 RelatedThe 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
Hi everybody,
I would like to know if there is any property in sql2000 database to separate lowercase characters from uppercase characters. I mean not to take the values €˜child€™ and €˜Child€™ as to be the same. We are transferring our ingres database into sqlserver. In ingres we have these values but we consider them as different values. Can we have it in sqlserver too?
Hellen
What I am trying to create a query to check, If recDT is not value or null, then will use value from SELECT top 1 recDtim FROM Serv. Otherwise, will use the value from recDT. I have tried the below query but it doesn't work. The error says, Incorrect syntax near the keyword 'SELECT'.Incorrect syntax near the keyword 'else'.1 SELECT
2 case when recDT='' then SELECT top 1 recDtim FROM Serv else recDT end
3 FROM abc
4
Anyone can help? Thanks a lot.
Hi!
I like a new sql editor instead of the query analyzer.
The QA dont show the outlining, bracket injuctions and intellilist.
Help!
Wich editor able this functions?
Just curious,
What do all of you use to create and edit stored procedures? I am looking for a better way to code than to use the default window supplied with Enterprise Manager.
Do most of you use the Query Analyzer? Are there third party products you like using better?
Thanks for the info..
Eugene
Hi everyone
I'm wondering if there's a better SQL Editor than MS Query Analyzer on the market? I like a lot of the functionality provided by QA but want extra stuff like you get in VB6: intelli-sense (sytanx prompting), auto-complete (CTRL+Space provides list of sp's and tables, etc.) plus any other time saving features.
I've tried a few products but nothing quite hits the mark. Is there a program you use and recommend I trial?
Cheers - Andy
Does anyone know if it is possible to use the XML Editor in SQL 2005 to write xml files from scratch?
If possible, how is the editor being started?
As of my current knowledge, I am only able to start the XML Editor by clicking on the content of a XML data type column within the sql query result pane.
Thanks
Mike
Hi,
I am looking for a sql script editor that enables printing in color like sql query analyzer does it on screen but unfortunatly not when printing.
any suggestion is welcome
thx
rv