Grabbing The First Letter Of A Field
Dec 1, 2005Hey all, i'm trying to build a little piece of code that will grab the first letter in a first name field, but I can't quite get it.
Any help would be great
thanks
Caden
ADVERTISEMENT
Help Grabbing Top Three Results From A SQL Query!
Jul 17, 2007I have a pretty big SQL query with a one to many sort of relationship...
There are client accounts that we're reporting on. Each account has four different historical categories attached. Each historical category can have maybe fifty entries in each, sorted by date.
I need to figure out how to grab the unique accounts and show only the three most recent results per each historical subcategory with the account... the three most current results from each of the four subcategories, displayed by the parent client account number.
I've created four views to isolate the subcategories as a start, thinking I could bring them into a parent query ...
but my question would be... how do I generate JUST the top three historical transactions by account number? If I select TOP 3, I get only the 3 newest in the MASTER LIST, not per client account, which is what I need to do.
Does this make sense? I could really use a good SQL helping hand with this one.
Thank you!
Grabbing The Difference Between Two Tables...
Jun 17, 2008Hi,
First post here :) Subject might not make sense, so allow me to explain. I have a table, tbl_finalized. A unique record consists of an emp_id, building, and school_year. I have another table, (call it table2) where emp_id, building, and school_year are fields as well. What I'm trying to do is... grab all the emp_id's from table 2 ( along with the school_year and building ) and then see if it exists in tbl_finalized. If it does, I don't want it. I want all the usernames in table2 that don't exist in tbl_finalized.
Here's an example:
table2 contains the following record:
emp_id: xxx, building: 333, school_year: 2008
emp_id: zzz, building: 333, school_year: 2008
emp_id: yyy, building: 444, school_year: 2008
tbl_finalized has the following records:
emp_id: yyy, building: 333, school_year: 2008
emp_id: xxx, building: 333, school_year: 2007
emp_id: xxx, building: 333, school_year: 2008
The result I desire is:
emp_id: zzz, building: 333, school_year: 2008
emp_id: yyy, building: 444, school_year: 2008
I hope this makes sense. If not, please ask and I will do my best to explain. I have light understanding of joins, but not sure how to get the difference like I desire in this case...
TIA.
Grabbing Characters From A String
Jul 23, 2005HelloI want to write a stored procedure (using Enterprise Manager) that can grabthe digits that are inbetween the two dashes (-) in strings like:123-150-401-123-832-4215-61The digits to the left, right and inbetween the dashes could be any length,so a static "get the 5th, 6th and 7th digit" stored procedure won't work.Many thanks,--Chris Michaelwww.INTOmobiles.comDownload 100s of ringtones, wallpapers & logos every month for only £1.50per week
View 1 Replies View RelatedGrabbing A Value From Listbox To Query Database
Feb 1, 2006hello forum,
I need to grab a string value from a list box in from a web form, and pass it to a sql select command statement where that value is equal toall values in a database table(sql 2000).
example
zip code list box3315433254 845788547535454 selected value is 85475
I am putting that value in a string like this:
dim string_zip as stringstring_zip = zip_ListBox.text
Question, how do i pass that value to sql stament, i am using this but does not work.
SqlCommand1 = New SqlCommand("SELECT zip FROM table WHERE zip = string_zip", SqlConnection1)
Grabbing Accounts That Doesnt Have All Possible Values
Feb 9, 2007I have a table:
Code:
serverid | value
12 | languages
12 | php
12 | coldfusion
12 | mysql
12 | mssql
12 | asp
14 | languages
14 | php
14 | asp
16 | languages
16 | php
16 | coldfusion
16 | mysql
16 | mssql
15 | languages
15 | coldfusion
15 | mssql
i need to get all serverid of those that have entries for "languages", but dont have rows for php, asp, and coldfusion.
so in the above example, i would get 15 and 16.
i dont think i can do in one statement?
sql Code:
Original
- sql Code
SELECT t.serverid FROM table t WHERE t.value = 'languages' AND (t.value != 'php' AND t.value != 'asp' AND t.value !='coldfusion')
SELECT t.serverid FROM TABLE t WHERE t.value = 'languages' AND (t.value != 'php' AND t.value != 'asp' AND t.value !='coldfusion')
i think that above statement is completely wrong already ( I think it would need to do a select statement inside the WHERE clause)
i think i would need to maybe create a temp table and use a cursor?
any ideas or help ?
Executing Two Tables And Grabbing It's Data.
May 2, 2008Hi,
I am passing a parameter and executing two tables and grabbing it's data.. In the future I will put the code into a store-procedure.
--Exec Table 1
declare @id varchar(20), @MEMBER_ID varchar(20)
set @id=null
set @MEMBER_ID ='55555' --ie. 55555
Select id from emp Where MEMBER_ID = @MEMBER_ID
--Okay, Next I need to execute another table and pass in the id
--that was selected from the emp table.
SELECT EMAIL FROM moreInfo WHERE id = @id
Currently, the emp table displays ie. 100 records that matches the member id 55555.. But the second select is empty.. And I need to display email data for the 100 records that were selected from the emp table..
I hope is it not confusing what I am trying to do..
Please tell me how to do it..
Thank you...
Problem Grabbing Correct Data On A Join
Apr 21, 2008Hi folks,
I'm a little new to SQL programming but I'm learning. :)
I need to do a join on a table where I exclude records that have more than one urn (unique record number).
In the example table below I want to exclude both entries in the cr_urn column of 49074 & 49075.
cr_urn proc_urn crs_seq crp_seq crp_site
49073233311NULL
49074205111NULL
49074261512NULL
49075128011NULL
490752185121945
49076233311NULL
49077233311NULL
490781145121875
To get to this ->
cr_urn proc_urn crs_seq crp_seq crp_site
49073233311NULL
49076233311NULL
49077233311NULL
490781145121875
SQL Server 2014 :: Insert Dataset Into Two Tables And Grabbing Identity From One For Other
Jan 2, 2015Ok I think I will need to use a temp table for this and there is no code to share as of yet. Here is the intent.
I need to insert data into two tables (a header and detail table) the Header Table will give me lets say an order number and this order number needs to be placed on the corresponding detail lines in the detail table.
Now if I were inserting a single invoice with one or more detail lines EASY, just set @@Identity to a variable and do a second insert statement.
What is happening is I will be importing a ton of Invoice headers and inserting those into the header table. The details are already in the database across various tables and and I will do that insert based on a select with some joins. As stated I need to get the invoice number from IDENTITY of the header table for each DETAIL insert.
I am assuming the only way to do this is with a loop... Insert one header, get identity; Insert the detail table and include the IDENTITY variable, and repeat.
Grabbing Mobile Data For Desktop Ui (update/delete/insert)
Mar 11, 2008Howdy,
Am trying to find a way to insert/update/delete data in a SQL mobile database on a Windows CE 5.0 device FROM a desktop PC.
This situation is completely stand alone, no network (apart form device/desktop), no GPRS etc etc etc.
I've looked at RDA but i dont believe it fits my app. (pulling data from a 2005 server that doesnt exist doesnt really help me much, push can't be used without a pull which kills the idea.)
The goal is a UI on the desktop that can manipulate data in the SQL mobile Database.
I've tried all i can find/think off in relation to this but to no avail.
My latest attempt has been using the simplest method possible (using a VS wizard datasource to the devices DB and tryign to whack that on a form) but this just creates a "Path not found. Check the directory for the database [Path = Mobile Device/ce_swipe/TestDB.sdf".
Get The First Letter
Mar 24, 2008I have a table of definitions:
---------------------------
Glossary
---------------------------
Term | Definition
---------------------------
What I need to do is find all the letters that are at the beginning of the terms.
So if I have "apple" as a term, A is returned.
Also, if I have "forest", "fruit", and "flower", F is returned, but only once.
And if I have no terms that start with Q, Q is not returned.
Lastly, if I have a term like " 'Walking' Pneumonia", it should recognize that the term starts with a W.
Get First Letter In A Sql-query
May 6, 2007Hello!I want to get the first letter in a sql-query and compare with a QueryString.This is the pricip in ASP Classic:Select * From Dictionary Where Left(_Name, 1) = Request.Querystring["Letter"]I think you'll understand.Thanks.
View 1 Replies View RelatedHow Many Times Does A Letter Appear In A Name
Nov 30, 2006i have a column containing employee name with datatype varchar.
i want to find out that how many times does letter 'y' appear in employee's name.
am a beginner to sql
help appreciated
cheers
Getting Only First Letter Of The Column
Jun 29, 2007
Hi,
I am using an OLEDB source to run a stored procedure, which returns records from a temp table. The destination table is exactly same as the temp table in the stored procedure. I've some collation settings on both the destination table and temp table, but both are exactly same.
I got Unicode to Non-unicode conversion error first. I dont know why it happened as there is no difference in source and destination table. I solved that issue using the data conversion component. Then I got the truncation error. I set the Ignore on Truncation on error output to get rid of that issue. Then the package executed without any problem. But all the nvarchar and varchar fields in the destination table got populated with only the first letter of data.
Any Idea?
Drop Down List - Use More Than 1st Letter
Apr 26, 2005In my drop down menus - which are populated by tables - we can only use the scroll bar OR type in the first letter to find the item - Is there a way to type in several letters to find something? (e.g. if I want to find clockwork - right now I could type in "C" to get to the C's - but if I tried typing "clo" it would end up at the beginning of the "O's" - Thanks
View 1 Replies View RelatedNot Display The Letter Unless Count &>= 3
Jun 3, 2008SELECT name = SUBSTRING(name, 1, 1),
total = COUNT(SUBSTRING(name, 1, 1))
FROM products
GROUP BY SUBSTRING(name, 1, 1)
the assignment said that,
quote:"Do not display the letter and count unless at least three product names begin with the letter."
but i got the errors when I try following.
WHERE total >= 3
quote:Msg 207, Level 16, State 1, Line 4
Invalid column name 'total'.
WHERE COUNT(SUBSTRING(name, 1, 1)) >= 3
quote:Msg 147, Level 15, State 1, Line 1
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
so what should I do?
Replacing First Letter Only In Column
May 17, 2006Hello. We have a list of 1000+ sku numbers. 90% of them start with u. I need to replace the u with T.
Concerns:
It has to only replace the first letter, if there are other "u"s in the sku, they need to remain.
Not all skus start with u, those that don't should remain the same.
I have searched and searched because I'm sure someone has requested the exact same thing but I can't find anything that is identical.
I have this so far but I know its not correct:
set sku = replace(left(sku,1),'u',right(rtrim(sku),1)+
'T')
Can someone help lead me down the right path?
Thanks in advance!!!
Increment Char Or Letter
Aug 17, 2007I'm looking for a way to do something like this:
A, B, C, ... Z, AA, AB, AC
Use Of Capital Letter N In Script
Dec 29, 2007Below is from the SQL Help files on INSERT INTO
http://msdn2.microsoft.com/en-us/library/ms174335.aspx
What is the N doing in this script? (...N'F2'...N'Square Feet'...)
-----------------------------------------
USE AdventureWorks;
GO
INSERT INTO Production.UnitMeasure
VALUES (N'F2', N'Square Feet', GETDATE());
GO
-----------------------------------------
TIA.
Changing Drive Letter
Jul 20, 2005Hi there. I have to change the drive letter where SQL server have hisdatfiles (master included). If I do that, ob the SQL fail to start, becouseit's unable to find the master datfiles. Is it possible changing driverletter without reinstalling the complete product?BRamnésia
View 2 Replies View RelatedFirst Letter Capital More Than One Word
Oct 26, 2006Here is my question: Can you set this code up to do more than one word ex: I have names like Mc Donald, or Brook-Smith and the are lower case like the code states??
upper(left(client.first,1))+ lower(right(client.first,len(client.first)-1)) +' '+ upper(left(client.last,1))+ lower(right(client.last,len(client.last)-1)) As 'Full Name'
Using 1 Report To Print In Both Letter And A4
Aug 7, 2007I am looking for a way to, at execution, have 1 single report either print in "letter" or "A4" depending on a parameter I send it... anyone have an idea on where to start on this?
Thank you,
Brian
Convert 1st Letter To Uppercase In SQL Comand
Aug 15, 2005Hi 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??
Specifying Drive Letter For Data And Log Devices
Sep 25, 1999Hi am new to this, I am doing some asp development and wanted to know if there is a way of specifying the drive to place log and data devices through sql statments or stored procedures.
--Tito
How To Get The Drive Letter And The Backup Directory
Jan 16, 2008Hello,
I'm looking to get the drive letter and path for my backups programmatically. I am monitoring my sqlserver boxes remotely and need to get this information to determine if my backups will fit on the backup drive.
I'm looking for either a sql statement or a stored procedure to accomplish this.
For example...the backup script states:
EXECUTE master.dbo.xp_create_subdir N'H:MSSQLBackupmaster'
I need to know how this script gets H:MSSQLBackup
Thanks in advance.
Update Column Based On 1st Letter
Feb 25, 2014I have a large website with over 100,000 images and the location of the images are stored in a column (img_url) as below:
/images/imagename.jpg
Because all these images are stored in the same folder it is hard to manage so we want to store each image under a directory based on the 1st letter of the image name, ie:
/images/a/aimage.jpg
/images/b/bimage.jpg
I can automate the physical move of the images into the correct directory but I need SQL update query that will update each column based on the 1st letter of the image.
So:
/images/aimage.jpg
/images/bimage.jpg
Updated to:
/images/a/aimage.jpg
/images/b/bimage.jpg
Mapping A Number To A Letter Grade...
Mar 24, 2008I'm trying to think a way to map an average rating (1-10) to a letter grade (A-F).
For example, if I querry the name of a professor and the corresponding rating for the professor, then I would also like to generate a column displaying the corresponding letter grade for the professor based on the rating.
Professor | Rating | Grade |
Smith 8.5 B
I use an aggregate function to diplsay the rating.
I'm thinking I may need to write my own aggregate function to display the letter grade. 'Professor' is an actual field of the database.
Skip Rows Where The First Column With Letter 'S' - BCP
Aug 9, 2006Hi All,I have this data file with fix length(see below). I am able to insertit into the database using bcp, but now I want to skip (do not insert)the row which start with letter 'S' into the database. Is there away todo it? By the way I am using -F2 option to skip the first record.Here is my data:Record 1 04XXX2 13106900240120042003040045061 Testing N POLYDOROS TRUSTEEE2 12621241640280041004040045633 What are they MARTIN &XXXXXS C1000003200400409850000059611000000500001000000001 9613000000576497500S X1000003200000209850000059613000000000000000000001 9613000000573497000Thanks for your help.Ted Lee
View 1 Replies View RelatedCapitalizing First Letter, And Lower The Rest
Oct 25, 2006I am trying to get this code to work: (left(religion,1))+ lower(right(religion,len(religion)-1))As Religion
I am getting this error:
Invalid length parameter passed to the RIGHT function.
Preventing A Page Break For A Letter
May 20, 2008I have a letter I am creating in SSRS and would like the data to flow smoothly from one page to the next even if the whole section doesn't fit on a page instead of it forcing a page break which results in a large blank space on the page before. I have put the data into multiple text boxes (can grow is set for the text boxes) because of formatting requirements (I also tried the table object).
--Start Example--
TEXT HEADER 1
very long report letter information here (call it detail 1)
TEXT HEADER 2
even more very long report letter information here (call it detail 2)
TEXT HEADER 3
The term ""Accountant€™s Letter"" as used herein shall mean the certain math verification report, to be provided by an independent certified public accountant, certifying that the Securities shall generate the debt service payments due under the Loans. (call it detail 3)
--End Example--
So assume that everything but detail 3 fits neatly on the first page. Now part of detail #3 should fit on page one but it pushes the whole section to page 2 because it doesn't all fit. All I want is for say line to to be the last line on page #1 and lines 2 and 3 to be in page 2 instead of lines 1 through 3 on page 2. In reality, this letter is about 6 pages and each section is about a half page to 2 pages. This perceived minor issue has cost me a good day+ set back. Thanks,
Search Not Working With String Consisting Of Two Letter
Feb 25, 2007hi friends,
I have search in my project based on strings(surname, firstname).. but it does'nt works wen name consists of only two letters.... could somebody please help me on this.
Thanks sandeep
How To Store Resume And Cover Letter Into SQL Server?
Jun 6, 2005Hi,
I am trying to create an asp.net web recruiting application for HR, which will give the users the ability to both copy/paste the Resume and cover letter in textbox and upload resume and cover letter, then submit it (which will be saved into SQL Server 2000 table).
I am thinking to save Resume and cover_letter as Image data type columns in SQL Server.
. Can someone give me a direction about how to save the uploaded resume and cover letter to table and if it's the easiest way to do it?
. What to deal with different formats of uploaded resumes? I hope to limit to only Word or HTML
. Since I also give user another option - copy/paste the resume and coverletter into a textboxes. Can I simply save the copy/paste resume and cover letter into text field column? Later, say, if any HR recruiter retrieve the text from database, will it concatonates everything together without line break?
Any ideas is appreciated.
Restoring Master,Model To Different Drive Letter
Jan 26, 2001Hi All,
I took a backup of master,model and msdb database which was on D: drive and i tried restore it on C:. Now if i try to start sql server it comes out with a erorr that model database cannot be found and it keeps looking for model in D: drive which is not there.I tried restore of model with move option but it says cannot drop model as its system database.I tried attach detach but it does not understand tempdb as temp db is also on d: and i cannot understand how to move or point it to c:
Suggestions will be greatly appreciated
Vishal