Can someone comment on why joining two tables on CHAR fields of different lengths would generate unexpected results?
I had an issue where I ran an update that used an inner join on two tables. The field I used in the join was char(50) in one table and char(13) in another table. The result gave bad matches. After changing the field types both to varchar(30), the problem was eliminate.
I want to query TABLE1, row 1 so that I pull back the Names for the values stored in the ID and Other_ID fields so that my results are like: John Bob Number1
The only way around it now is that I store Other_Name in Table1.
Is it possible to tell sql server to cast to a datatype and set thefield length to a variable.e.g. :-declare @flen intset @flen = 10select (cast somefield as char(@flen) newfield)into newtablefrom sometableI have also tried :-select (cast somefield as char(max(len(somefield))) newfield)into newtablefrom sometableWhen I try the above examples I get error in @flen; error in maxrespectivly.TIASimon
Suppose I store cleartext strings in a field declared as varchar(100). Is there any way to know the minimum varbinary column sze to use for the encrypted data? (e.g. should it be varbinary(100) or (200)?, (8000)?). I'm sure it's algorithm specific but I don't know what factors influence the final length.
Hi ,Have a Visual C++ app that use odbc to access sql server database.Doing a select to get value of binary field and bind a char to thatfield as follows , field in database in binary(16)char lpResourceID[32+1];rc = SQLBindCol(hstmt, 1, SQL_C_CHAR,&lpResourceID,RESOURCE_ID_LEN_PLUS_NULL , &nLen1);and this works fine , however trying to move codebase to UNICODE antested the followingWCHAR lpResourceID[32+1];rc = SQLBindCol(hstmt, 1, SQL_W_CHAR,&lpResourceID,RESOURCE_ID_LEN_PLUS_NULL , &nLen1);but only returns 1/2 the data .Any ideas , thoughts this would work fine , nit sure why loosing dataAll ideas welcome.JOhn
I am trying to join two tables and the datetime fields need to be part of the join. They contain times in the fields too. I just need them to join on the date part though. Is there maybe a way to just return the mm/dd/yyyy format and join on that? Any and all help is appreciated.
I have a sql 2005 dev ed running an application developed in vs.net 2005 C# I have several gridviews which have a field call first name and a field called lastname I need to put both the firstname and lastname in the same cell or colum row. I do not now how to join these. Can some one help me with a SQL query string that will do this for me.
Question.I have a new table that I am adding to a script that I wrote. Thistable has 3 fields, the first 2 fields are used in the on statement asbeing = other fields in the script.The first field always has data in it, but the 2nd field is sometimesnull.So my problem is if both fields have data in them and they both matchto the data in the fields that I am linking them to, then it returnsthe 3rd field without a problem. However if the 2nd field is null thenit is returning a null for the 3rd field. I have checked and the fieldthat I am linking to is null also.So if I haveselect t1.field1, t1.field2, t2.field1, t2.field2, t2.field3from table1 t1join table2 t2on t1.field1=t2.field1 and t1.field2=t2.field2with 2 records in each tabletable1: record1: data, datarecord2: data, nulltable2: record1: data,data,datarecord2: data,null,datawhat I get from the script isrecord1: data, data,data,data,datarecord2: data,null,data,null,nullI would expectrecord2: data,null,data,null,dataI hope this makes sense, I didn't want to post the entire actual scriptas it is about 150 lines long.Thanks in advance.
I have a simple query which displays items from inventory with their latest annual test date. I want to create another unique reference in my results to use as a certificate number. The number should be a combination of the item+month+year from the test date. What is the easiest way to accomplish this?
My query and my desired results are below:
select item, test_date from inventory where cat = 'TELE' and itemised_status > 15
I have a test query that i have ordered by the expected result order.
select t from ( select cast('1' as nvarchar(20)) as t union all select '1---qa' union all select '1q' union all select '1q1' union all select '1qz'
[Code] ....
and the question is... why is minus character (at 3rd line in '1---qa' string) ignored by "order by" clause? That row is put after '1q1' and not after '1'.
When you replace '-' with '+' or any other special char it works as expected but not with '-'. It is just ignored no matter if there is one or more of them.
Don't mind local chars like čž, they are for testing purposes with collate (thus commented) and with bin collation that is not doing proper sorting but minus order is ok in that case.
Tested on SQL 2008 R2 and SQL express 2012, database collation is 'Croatian_CI_AS'.
I'm extracting a char(2) field from a table that has a value of '1' and writing it to another table but want the output field to be padded with a zero. I have tried this below:
right('00' + isnull(Field1, ' '),2
but the output field comes out as '1 ' (that's 1 followed by a space). Does anyone have any idea how I can do this or please point out what I'm doing wrong in my 'right(' fucntion above. Thanks, Jeff
I have been asked to investigate the feasibility of converting an Invoice Number field from 6 to 7 digits, and allowing alpha characters. This means CHAR. The Invoice Number is not used outside of the application, but changes would have to be made to the database, and VB code that runs the app. Any ideas on how to proceed?
Any responses would be greatly appreciated! Thanks
ok, so we have a field in one of our db's, a do_not_synch field. this field has a datatype of char...
the following sql statement does not update the field..
update contacts set do_not_synch = 'Y' where id=1
and if i open up the table in sql server management studio, and cannot type in and save the value Y...yes i can update other fields in this row...so its not a permissions issue?
I have a varchar field that contains values to represent the month and year in the format mmyy (eg. 0107, 0207, 0307 etc).
I want to be able to select the value that is 6 months before the current month and year, for example if I were running the query in the current month/year (April 08) I would return the value 1007 (October 07).
If I want to make a field of characters to be unlimited length(or maybe 2k for example), what datatype should I use? Char, varchar and text have a max. limit of 255...
Here is the example data <qMultipleChoice><qText>The%20AE%20understands%20what%20conditions%20the%2 0Account%20Manager%20is%20allowed%20to%20sign-off 20on.</qText><qChoice>Strongly20Disagree</qChoice><qChoice>Disagree </qChoice><qChoice>Agree</qChoice><qChoice>Strongly%20Agree</qCh
I want result look like this First Column:The AE Understands what conditions the Account Manager is allowed to sign-off. Second Column: Strongly Disagree Disagree Agree Strongly Agree
This is what i had so far Select (SUBSTRING(QuestionText, (PATINDEX(N'%<qText>%', QuestionText) + 7),(PATINDEX(N'%</qText>%', QuestionText) - (PATINDEX(N'%<qText>%', QuestionText) + 7)))) From tblQuestion
my result: The%20AE%20understands%20what%20conditions%20the%2 0Account%20Manager%20is%20allowed%20to%20sign-off%20on.
I have problem with replace '%20' and how to make the second column. Any Help? Thanks Shan
I know there has to be a way to do this, but I've gone brain dead. Thescenario..a varchar field in a table contains a date range (i.e. June 1,2004 - June 15, 2004 or September 1, 2004 - September 30, 2004 or...). Theusers have decided thats a bad way to do this (!) so they want to split thatfield into two new fields. Everything before the space/dash ( -) goes intoa 'FromDate' field, everything after the dash/space goes into the 'ToDate'field. I've played around with STRING commands, but haven't stumbled on ityet. Any help at all would be appreciated! DTS?
I have a table with several million rows of data. There is a date field defined as a char(8) with some bad rows. i tried to locate them with below
select date_stopped from patient_medication where isdate(convert(datetime,date_stopped)) = 1
This won't work, I get the Server: Msg 242, Level 16, State 3, Line 1 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value
I am attempting to find quotes (") in a column and replace with the string '--THIS-WAS-QUOTES--'. Right now my script only converts the first quote it finds in the description column, converts to the string and moves to the next row leaving the other quotes as they were. Below is my query script
I am attempting to find quotes (") in a column and replace with the string '--THIS-WAS-QUOTES--'. Right now my script only converts the first quote it finds in the description column, converts to the string and moves to the next row leaving the other quotes as they were. Below is my query script
Would it be OK to use varchar(5) instead of char(5) as the first field of acomposite clustered index?My gut tells me that varchar would be a bad idea, but I am not finding muchinformation on this topic on this when I Google it.Currently the field is Char(4), and there is a need to increase it to hold 5characters.TIA
Is there a way to extract the date part (11/27/2012) of a datetime/time stamp column (11/27/2012 00:00:00.000) and keep it in a date format?
The code i have below extracts the date part of a timestamp column and converts it to a char field. This becomes a problem when I joing the resultant table with a SAS dataset which contains the same column but is in a date format. The join process generates an error saying the column is in different formats.
Hi, What is the difference updating a null value to char/varchar type column
versus empty string to char/varchar type column?Which is the best to do and why? Could anyone explain about this?
Example:
Table 1 : tCountry - Name varchar(80) nullable Table 2 :tState - Name char(2) nullable Table 3 :tCountryDetails - countryid,state (char(2) nullable) - May the country contain state or no state So,when the state is not present for the country ,i have two options may be - null,'' tCountryDetails.State = '' or tCountryDetails.State = null?
I have followed many tutorials on selecting and replacing text in text fields, varchar fields and char fields, but I have yet to find a single script that will to all 3 based on field type. Let's assume for a moment that I don't know where all in my database a certain value that I need changed resides ... i.e., the data's tablename and fieldname. How would I go about doing the following ... or more importantly, is this even possible in a SQL only procedure?1) Loop over entire database and get all user tables2) Loop over all user tables and get all fields3) Loop over all fields and determine the field type4) switch between field types and change a string of text from 'a' to 'b'Please be gentle, I'm a procedure newb.
Is there a way to compare two dates fields that are different in length? For example, I have table1.datefield with a date of '040808' and table2.datefield with a date of '04082008'. Just comparing the two fields is not coming up equal. Has anyone had this situation before?
I am working with a table that has zip codes listed in lengths of 9,8,5 and 4 digits. The table is created this way and I have no way of changing the data outside of SQL. I am trying to get the last four digits off of all the zip codes so that I only have to work with zip codes in lengths of 5 and 4
I just learned that bit masking a varbinary column can increase it's length in bytes unnecessarily. For example, I ran the following...
declare @v1 varbinary(max)
set @v1 = 0x0100AB
select len(@v1)
set @v1 = @v1 | (len(@v1) - 2) * 256
select master.dbo.fn_varbintohexstr(@v1)
select len(@v1) ...and get 3 0x00000000000101ab 8
This messes up a plan I had for varbinary column use.
So I quickly tried the following to look for strategies to deal with this unwanted growth...thought being that while I'm passed a varbinary(max), operating on a varbinary that matches its passed length would avoid the unwanted growth after bit masking....
declare @i int
set @i = 5
declare @v2 varbinary(@i)
...but got errors.
So I suppose I can use a combo of the len, substring etc functions to correct the situation after bit masking but would like to know if the more elegant approach of dynamically sizing a varbinary is possible in t-sql, or if perhaps there is a way to prevent the unwanted growth during bit masking.
