Querying on first few characters in a field


I have a query that pulls records based on data matching an ID field. Normally, the ID's are 3 digits and are all that exists in the ID field. However, the ID field sometimes contains two ID's (comma separated). So usually you have "735" or "421" in the ID field, but sometimes you have "736,737" or "429,430".

Can I use the length() function or truncate inn order to just query on the first 3, before the comma Or some sort of TRIM type function that will do that

Posted On: Saturday 10th of November 2012 05:07:31 AM Total Views:  95
View Complete with Replies

RELATED TOPICS OF Microsoft SQL Server PROGRAMMING LANGUAGE




Loop to get SQL field lengths

Hi all, I have a table with about 87 fields in it. Is there a way I can write a query to get the lengths of all the fields, other than writing something long like: Code: SELECT Len(Field1), Len(Field2)......Len(Field85) From TABLE Sanctos
VIEWS ON THIS POST

192

Posted on:

Saturday 10th November 2012
View Replies!

German characters

I would like to know if SQL supports German characters stored as varchar
VIEWS ON THIS POST

90

Posted on:

Saturday 10th November 2012
View Replies!

Cannot ORDER BY on certain field types

I have an ASP page that I'm using to display a recordset and allow users to sort on column headers (field names). However, I keep getting the message "... The text, ntext, and image data types cannot be used in an ORDER BY clause" even when I have changed the field type to char, nchar, or nvarchar. I take it these are identical to text, ntext but just named differently There has to be a way to sort by fields containing text -- any suggestions Thank you!
VIEWS ON THIS POST

128

Posted on:

Saturday 10th November 2012
View Replies!

Error querying against linked FoxPro db

I have an issue querying against a FoxPro database. If I do: Code: select * from foxpro...train I get: Error converting data type DBTYPE_DBTIMESTAMP to datetime. This caused by one column, duedate. Code: select cast(duedate as varchar(30)) from foxpro...train it gives the same error. So it seems some implicit casting is being done even before I get any data. Unfortunately of all the data in that table, that is the one column I can't drop. This data is from a legacy application (we don't have foxpro as far as I know) and I am trying to import it into a SQL database. Works fine except for a handfull of tables with these date issues.
VIEWS ON THIS POST

214

Posted on:

Sunday 11th November 2012
View Replies!

Auto increment field using UPDATE statement

I have a field in my table called 'Hits'. I want it to increment by one each time the form is submitted. I have come across this code from the web and have incorporated it into my code and it seems to do what I need but when I run it nothing happens:- Code: DECLARE @counter int SET @counter = 0 UPDATE users Set @counter = Hits = @counter + 1 Where Sitename = '" + Site.Text + "' I can't have my 'Hits' field as the Primary key and then increment this way because there will be different 'Sites' picked from my form. So for Site 1 it could have 20 in the 'Hits' field. For Site 2 it could have 10 etc etc. So I want to have an increment or running total for each site. Is there a better/easier way of doing this
VIEWS ON THIS POST

198

Posted on:

Sunday 11th November 2012
View Replies!

Inserting usernames based on firstname and lastname

Hi i have customer table with username field what i want to do is when a customer enters his firstname and last name from the front end , i would create a username based on the firstname ,lastname ex: John Carpenter then username is johnC since the username should not exceed 8 characters . If JohnC already exists the it is should insert JohnC1 and if johnc1 exists JohnC2.... If user firstname is jennifer lastname: Houston then username is JennifeH removing r from jennifer but if that already exists then jennifH . It should again check for to see if jennifH exists . If yes the it adds JennifH2...so on. the main criteria is that the username should be only 8 characters. Hope i am clear .
VIEWS ON THIS POST

113

Posted on:

Sunday 11th November 2012
View Replies!

Indexing the char field as integer

I have some character data stored in the database but when i am trying to index its not providing me results as required for example after indexing the data i can see in the order as 1 111 1111 12 2 21 but i need in the following order 1 2 12 21 111 1111 i have used the statement below Code: select col_1 from orderTable order by convert(int, col_1) but i am wondering if i can do the same and create index based on converting the filed into int is it possible , kindly share any example code.
VIEWS ON THIS POST

100

Posted on:

Sunday 11th November 2012
View Replies!

INSERT INTO Statement syntax and special characters

I have a few tables in SQL Server, an ODBC link set up on my computer, and an Access Database front end. In my form I am populating textboxes from another database and a few of the values have Apostrophes in them '.....my SQL INSERT INTO statement is not working on the records that have Apostrophes... The fields in the SQL database are text fields....Is there anything that I can do to get the SQL update to work Syntax wise Of do I have to go through an remove the Apostrophes' and periods . etc THis is an example of the textbox value: Kingston's Meadow This is the code I am using... THANKS Code: varName = Text82 varOwners_Residence = Text84 varNotice_Expiration = cbo_94 varNotes = Text104 varNotice_Number = Text80 varStatus = Text88 'varResides = "" 'varturn_in = "" varNotice_Date = cbo_92 varProperty_Address = Text86 varNuisance_Type = Combo98 varAddition_Name = Addition_Name varRange = Range varLot = Lot varBlock = Block varZip_Code = Zip_Code varDisplay_PID = PID varSec_tion = Sec_tion varTownship = Township varProperty_City = Text106 varProperty_State = Text108 varProperty_Zip = Text110 varPictureID = Text160 varPicturePath = "\\weeds_database\Weeds_Database_Images\" varOwner_City = Text194 varOwner_State = Combo202 varOwner_Zip = Text196 Dim varID As Integer varID = Val(DMax("ID", "dbo_data")) + 1 'MsgBox varID '=============================================== '=============================================== Dim MySQL As String MySQL = "" 'MySQL = "INSERT INTO tbl_TRAINING (VendorNumber, FirstName, LastName, Dates, TimeLength, Type, Hours, Experienced_Judge, Attended) values ('" & varVendorNumber & "', '" & varFirstName & "', '" & varLastName & "', '" & varDates & "', '" & varTime & "', '" & varType & "', '" & varHours & "', '" & varExperiencedCheck & "','" & varAttendedCheck & "');" MySQL = "INSERT INTO dbo_data (" MySQL = MySQL & "ID,Name,Owners_Residence,Notice_Expiration,Notes,Notice_Number,Status,Notice_Date,Property_Address,Nuisance_Type,Addition_Name,Range,Lot,Block,Zip_Code,Display_PID,Sec_tion,Township,Property_City,Property_State,Property_Zip,PictureID,PicturePath,Owner_City,Owner_State,Owner_Zip" MySQL = MySQL & ") values (" MySQL = MySQL & "'" & varID & "','" & varName & "','" & varOwners_Residence & "','" & varNotice_Expiration & "','" & varNotes & "','" & varNotice_Number & "','" & varStatus & "','" & varNotice_Date & "','" & varProperty_Address & "','" & varNuisance_Type & "','" & varAddition_Name & "','" & varRange & "','" & varLot & "','" & varBlock & "','" & varProperty_Zip & "','" & varDisplay_PID & "','" & varSec_tion & "','" & varTownship & "','" & varProperty_City & "','" & varProperty_State & "','" & varZip_Code & "','" & varPictureID & "','" & UpdateFullPath & "','" & varOwner_City & "','" & varOwner_State & "','" & varOwner_Zip & "'" MySQL = MySQL & ");" DoCmd.SetWarnings False 'MsgBox MySQL DoCmd.RunSQL MySQL
VIEWS ON THIS POST

100

Posted on:

Sunday 11th November 2012
View Replies!

MS Access to MS SQL - Memo field - HELP :(

, I have just converted an MS Access database to MS SQL 2005 database using MS SQL Server - Import and Export data, and I just seem to be having problem after problem... it converted MEMO fields to nText, but then some of my sql queries stopped working and it said : "The data types ntext and varchar are incompatible in the equal to operator", so I then changed them to nvarchar(Max) as they need to contain a large amount of content, but then although there is no error, the contents of the fields are just not showing, as if they are blank or empty I keep searching on Google, asked anyone I know, and am just not having any luck - hence my message here Please can anyone offer me any advice or tell me where I am going wrong, I would be so so grateful. THank you in advance, Jane
VIEWS ON THIS POST

82

Posted on:

Sunday 11th November 2012
View Replies!

delete a field help?

I have a user who has submitted a survey twice, and I have to delete the second entry. But every user has a UserID and the data for the second submission has the same UserID (the only thing different is the fact she submitted data 5 seconds later)as this is one of the unique values used in many tables. So I have deleted data in other tables but If I try to delete it from this particulat table I can't as I am really trying to delete that ID, so how can I get rid off that record The record is in a view if that makes any difference.
VIEWS ON THIS POST

107

Posted on:

Sunday 11th November 2012
View Replies!

Inserting string into datetime field

I have 3 drop downs on my web form called dob1, dob2 and dob3. these represent the day, month and year. I want to add these into my db field called ClientDOB. But ClientDOB is a datetime field and I get the error:- cannot convert datetime to string. So I am trying to use a CAST statement to do this as follows:- Code: if Request.Form("dob1") "" and Request.Form("dob2") "" and Request.Form("dob3") "" then dateofbirth = Request.Form("dob1") & "/" & Request.Form("dob2") & "/" & Request.Form("dob3") sql1="INSERT INTO [user] " sql1 = sql1 & "(email, loginid, forename, surname, (CAST(ClientDOB AS datetime(20)) AS string(20)), dob)" sql1 = sql1 & "VALUES ('" & replace(request("email"),"'"," ") & "','" & ii & "','" & replace(request("first_name"),"'"," ") & "','" & replace(request("surname"),"'"," ") & "','" & replace(request("dob1") & "/" & request("dob2") & "/" & request("dob3"),"'"," ") & "','" & replace(request("memname"),"'"," ") & "')" conn1.Open Session("COGNISOFT_CONNECTSTRING2") rs1.Open sql1, conn1, adOpenDynamic conn1.Close But it's coming up with a syntax error, missing '(' It's pointing to this line:- Code: sql1 = sql1 & "(email, loginid, forename, surname, (CAST(ClientDOB AS datetime(20)) AS string(20)), dob)" Do I need to use a cast to convert it to a string or maybe a CONVERT statement
VIEWS ON THIS POST

96

Posted on:

Monday 12th November 2012
View Replies!

Text field is truncating at 50 characters....

I have wasted several hours on this so if anybody has a suggestion, I will be forever grateful.... I am pulling a text field from an sql database and when I write it out, the field is truncated to 50 characters even though it is really several hundred characters long. If I write out the length, it shows the correct length of the field. I tried using a substr in the select statement but that made no difference. (substr(Notebook.NoteDetail,1,255))
VIEWS ON THIS POST

121

Posted on:

Monday 12th November 2012
View Replies!

To reset a field back to it's default value at a certain date

I have a field which has a default value of 'No'. But when someone submits a form this value changes to 'Yes'. On certain dates I will need this value to change back to it's default 'No' again automatically. Is there any way of doing this automatically by itself Any thing I can set on the field's properties
VIEWS ON THIS POST

115

Posted on:

Thursday 15th November 2012
View Replies!

How to strip characters from a string?

See the sample data below. First Colum in the LawsonID. 2nd Column is the LegacyID. Code: 26022 000026022 27324 000027324 28893 000028893 27666 000027666 27254 000027254 27401 000027401 I wish to join on the LawsonID and LegacyID (both the fields are of type varchar) Given this scenario, how can I strip the the zeros away from the beginning of the string. No string starts with a zero while I was analyzing the data. However the catch is that some of the LegacyID are preceded by 4 zeros, while some are preceded by 5 zeros and some are preceded by 3 zeros. So how do I remove the pattern for the zeros before the number and then do a join between the LegacyID and LawsonID. Any suggestions and tips are welcome.
VIEWS ON THIS POST

111

Posted on:

Thursday 15th November 2012
View Replies!

Delete the first 441 records

I want to delete the 441 records from RegionalAnalysisAmtoutstanding after I have transfered them into Regionalssheet1 This is my query, but its not working right, instead of deleting the first 441 records its deleting 3977 records from RegionalAnalysisAmtoutstanding can anyone help please, I know its something very small but I can figure it out. Code: DELETE RegionalAnalysisAmtoutstanding FROM RegionalAnalysisAmtoutstanding a LEFT JOIN regionalssheet1 b on a.account = b.account WHERE b.account IS NULL
VIEWS ON THIS POST

77

Posted on:

Thursday 15th November 2012
View Replies!

Getting an exception while running the particular query first time

Hi Friends, we are getting an exception while running the following query first time. next time its executing properly. 05/13/2008 02:06:59|RxOrderServer_DEVTEST12_1|6|2|SQLQuery:erform_rs|Running query [GET_MCRORDERS_POF_RETRIEVE] 05/13/2008 02:06:59|RxOrderServer_DEVTEST12_1|6|2|SQLQuery_OS ql:erform| IN: QueryID=[22627] 05/13/2008 02:06:59|RxOrderServer_DEVTEST12_1|6|2|SQLQuery_OS ql::runQuery|Running query [SELECT A.ORDER_ID FROM SDA_POF.POF_FACILITY A, SDA_POF.RXORDER B WHERE A.WORK_TELEPHONE_NUMBER = '9725074641' AND B.ACCOUNT_ID = '6442224' AND B.ORDER_STATUS = 'MCR'AND A.ORDER_ID = B.ORDER_ID] 05/13/2008 02:06:59|RxOrderServer_DEVTEST12_1|6|0|LibExceptio n constructor|[SQLQUERY_Oracle::runQuery] - [0: [SQLQUERY_Oracle::runQuery]Error executing query] 05/13/2008 02:06:59|RxOrderServer_DEVTEST12_1|6|0|LibExceptio n constructor|[SQLQuery_OSql::runQuery] - [0: [SQLQuery_OSql::runQuery]Unable to run query 22627] 05/13/2008 02:06:59|RxOrderServer_DEVTEST12_1|6|1|SQLQuery_OS ql:erform|Running query [22627], will try alternate 05/13/2008 02:06:59|RxOrderServer_DEVTEST12_1|6|2|SQLQuery:erform|Query ID=[22627] 05/13/2008 02:06:59|RxOrderServer_DEVTEST12_1|6|2|SQLQuery:erform|Query SQL = [SELECT A.ORDER_ID FROM SDA_POF.POF_FACILITY A, SDA_POF.RXORDER B WHERE A.WORK_TELEPHONE_NUMBER = '9725074641' AND B.ACCOUNT_ID = '6442224' AND B.ORDER_STATUS = 'MCR'AND A.ORDER_ID = B.ORDER_ID] 05/13/2008 02:06:59|RxOrderServer_DEVTEST12_1|6|2|SQLQuery_OS ql:erform| OUT: QueryID=[22627] I want to correct this error. please help me what is wrong in this query. TIA
VIEWS ON THIS POST

239

Posted on:

Monday 19th November 2012
View Replies!

DatePart(), Adjusting first day of the week.

Here's my code and here's my dilema: SELECT DATEPART(ww, bil_date) AS Week, bil_date AS date FROM tm6user.billing WHERE (DATEPART(yyyy, bil_date) = 2006) GROUP BY DATEPART(ww, bil_date), bil_date Looks simple enough. I want a list of bil_dates grouped by week number (1-53). The problem is that I want Monday to be the first day of the billing week. My research shows that the GROUP BY line would be like this: GROUP BY DATEPART(ww, bil_date, vbMonday), bil_date But when I try to run this, I get this error: Microsoft OLE DB Provider for ODBC Drivers (0x80040E14) [Microsoft][ODBC SQL Server Driver][SQL Server]The datepart function requires 2 arguments. Any ideas
VIEWS ON THIS POST

78

Posted on:

Tuesday 20th November 2012
View Replies!

special characters within a SQL 7 DB

Ok.. so you need something in a DB to be... AT&T.... obviously this sometimes cause problems and sometimes not... Do you just replace the & with it's ascii value... & sometimes if i have to pull the db item such as 'AT&T Partner' the page prints 'AT' Or if i need 'Cabinets, KSU's & Something else' ... I get 'Cabinets, KSU' do I have to do a replace in and a replace out the ' i can avoid all together.. but I'm not getting out of AT&T
VIEWS ON THIS POST

152

Posted on:

Tuesday 20th November 2012
View Replies!

Scheduled job not getting completed on first run(takes more than 13 hrs to complete)

we are facing problem with Job performance it is taking hours to get completed : Requirement description: Requirement was to create a job which can run every week and can archive the old records (older then 365 days i.e. around 40000 Records) into an archive database and after Successful archival it deletes those from the live database. Implementation Description: I have created a scheduled job which calls "MASTER_SP" stored procedure; MASTER_SP Stored Procedure in turn calls two more stored procedures "ARCHIVE_SP" and "PURGE_SP" in a single transaction. ARCHIVE_SP Stored procedure archives the data from LIVE_DATABSE to ARCHIVE_DATABASE then PURGE_SP stored procedure Deletes that data from LIVE_DATABSE Problem Description: We took a database dump from the live environment and restored on testing environment, made changes to job and started the job for 40000 records(first run), job dint get Completed in 13 hours so I cancelled the job run. Then we tried with these tests and here are the statistics Second run 2500 records: job gets completed in 12 mins and 30 secs. Third run for 11000 records: job gets completed in 1 min and 30 secs. Fourth run 41000 records: Job gets completed in 1 min and 47 secs. Fifth run 113000: job gets completed in 4 mins and 30 secs. Sixth run 283000: job gets completed in 23 mins. We are facing the problem when job is run for first time on the restored database with huge number of records (i.e. around 40000 Records), CPU utilization is reaching to 100% on this machine, Here SQLSERVER process is utilizing CPU a lot. On the same restored database If we run the job with less number of records say 2500, then this job(i.e 2500 Records) gets completes in 12 minutes, and rest of the successive job runs(i.e. for 40000,113000, 283000) gets completed without any problem. statistics are shown above Can anyone say the exact reason why it creates problem on first run, please come up with some suggestions, so that i can implement them and move code into production.
VIEWS ON THIS POST

237

Posted on:

Monday 26th November 2012
View Replies!