Can't seem to get my head around this: I'm looking for a way to select only those varchar(10) values that soley consist of numbers. Leading spaces are allowed.
I tried using isnumeric, but it also allows those with periods, comma's etc.
Also tried using like, but the length of the varchar column varies too much to do a like '[0-9][0-9]....'.
As a solution I currently do a combo of isnumeric, not like '%.%', not like '%,%' etc. I need to do a conversion to an int to join another table, but the convert still fails. Not sure where and why.
I'm thinking there should be a better way than create a hughe list of "not like " but it looks like I'm in the woods here...
I have looked far and wide and have not found anything that works to allow me to resolve this issue.
I am moving data from DB2 using the MS OLEDB Provider for DB2. The OLEDB source sees the column of data as DT_TEXT. I setup a destination to SQL Server 2005 and everything looks good until I try and run the package.
I get the error: [OLE DB Source [277]] Error: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft DB2 OLE DB Provider" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
[OLE DB Source [277]] Error: Failed to retrieve long data for column "LIST_DATA_RCVD".
[OLE DB Source [277]] Error: There was an error with output column "LIST_DATA_RCVD" (324) on output "OLE DB Source Output" (287). The column status returned was: "DBSTATUS_UNAVAILABLE".
[OLE DB Source [277]] Error: The "output column "LIST_DATA_RCVD" (324)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "LIST_DATA_RCVD" (324)" specifies failure on error. An error occurred on the specified object of the specified component.
[DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (277) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Any suggestions on how I can get the large string data in the varchar column in DB2 into the varchar(max) column in SQL Server 2005?
I have a table Saleshistory with 89481412 records. I have a field Sales of nvarchar type, all the records in this field are numeric, and the records are with 2 decimal places. I expect all the records to be ending at .00 (for example 5.00,345.00,25.00), but I want to verify if something is for example 5.6 etc.
So in short I want to check if any Salesrecords have more than zero values in the decimal. How can I check this.
I have a column set to varchar(12) so I can ensure that the length of the string entered will never be more than 12 characters but I want to limit the string to a minimum of 8 characters, so I end up with a string that is from 8-12 characters long. How do I ensure the minimum length of 8 characters? I'm using SQL Server 2005 if that helps. I tried adding a check constraint like so:
DATALENGTH(UserName) >= 8
But I keep getting an error when I save the table, so any help would be very much appreciated.
I have a field defined as varchar(8) but this field should not contain any letters, needs to be only numbers. How can I validate the data if it contains only numbers? Any ideas?
I have two columns. policyNumber contains a 12-13 varchar string AllPolicyNumbersIncluded contains one or more 12-13 varchar strings (policy nums) seperated by commas
I want to check if policyNumber is contained in AllPolicyNumbersIncluded?
I have policyNumber LIKE AllPolicyNumbersIncluded which works when only one policy number is in AllPolicyNumbersIncluded and incidently works switched around AllPolicyNumbersIncluded LIKE policyNumber I assume because they are equal.
Can anyone tell me how to check if one column's value is a substring of another - without going through every possible substring of the second
Hi,I am writing a SP in MSSQL. One of the parameted is VARCHAR typewhich have comma seperated INT vlaues. I want to use this varible inWHERE clause against INT type coloum. how do I achive this.eg. DECLARE @CompanyTypeIDs VARCHAR(200)SET @CompanyTypeIDs = '1,3,2'Currently I am using it asSELECT CompanyTypeID FROM Company WHERE CompanyTypeID IN (1,2,3)CompanyTypeID is of INT type.But I want to replace the consts with the Parameter passed as belowSELECT CompanyTypeID FROM Company WHERE CompanyTypeID IN(@CompanyTypeIDs) - but this is not giving any result.Please let me know the solution.Many Thanks,Mahesh
Hi,I have a varchar column with values like:000 BNP=Item one HOP=Item two LLT=Item three001 LLT=Item one002 BNP=Item one LLT=Item twoI want to parse this rows into a table similar to the following output:000 BNP=Item one000 HOP=Item two000 LLT=ItemThree001 LLT=Item one002 BNP=Item one002 LLT=Item twoHas anybody done a similar assignment? Can you share me your solution?Thank you much!Edgar J.
In an effort to improve the speed of queries against my main table,I'll be indexing a column whose data type is varchar(50).Would I be better off (better performance) if I changed the column'sdata type to some numeric type? I would have to update the column'sdata to accomodate this, but I would do it if this offers aperformance gain.-- Bill
Hi Folks, Is it possible to add <a href=""http://www.yahoo.com"">xxxxxx</a> tag in varchar column as a text and display it as html hyperlink xxxxx (hyperlink). Thanks in advance Regards
Below is my query.Its working great if i remove ,Cast(C.ClassTime as time) as StartDate.But when i use this i get an error as The conversion of a varchar data type to a datetime data type resulted in an out-of-range value My ClassName is a varchar.Whose definition i cant change to DateTime now.But i want to cast it to DateTime.
Code: Select C.ClassID as Appointment_Id,C.ClassName as Appoitment_Descr,Cast(C.ClassTime as time) as StartDate,C.EndClassTime as EndDate, 'Class' as Type From Dojo D inner join DojoClass C on D.SchoolID = C.DojoSchoolID Where D.SchoolID = @DojoID and C.Days like'%' + @Days + '%' Union Select E.DojoEventID as Appointment_Id,E.EventName as Appoitment_Descr , E.EventStartDate as StartDate , E.EventEndDate as EndDate,'Event' as Type From Dojo D inner join DojoEvent E on E.DojoID = D.SchoolID Where D.SchoolID = @DojoID and @Date Between E.EventStartDate and E.EventEndDate
Is there a preferred way to order a varchar column numerically (for thosethat are numeric), then alphanumerically for all others?I've tried:ORDER BY CASE WHEN IsNumeric(<column_name>) = 1 THEN CONVERT(Float,<column_name>) ELSE 999999999 END;andORDER BY CASE WHEN IsNumeric(<column_name>) = 1 THEN 0 ELSE 1 END, CASE WHENIsNumeric(<column_name>) = 1 THEN CONVERT(Float, <column_name>) ELSE 999999END, <column_name>;Neither of these however give the desired results.Any ideas?Thanks,Frank
I have a column that I do not want any whitespace in whatsoever. I'mwondering how do enforce this a DDL level instead of in 40 millionseat-of-the-pants after-the-fact computer programs accessing thedatabase.Regards,Terrence
Hi all, I'm new at this SSIS but have been able to successfully create some simple packages. My situation is that at work we use a column to describe a status of applications. However, this makes for hellacious query because some of those statuses inherintly were one or more statuses previously. Example Admit = Admit Accept = Admit then Accept Withdraw Accept = Admit, Accept, then Withdraw Decline = Admit then Decline As you can see inherintly those were all admits at one point. So what I'd like to do is instead of having long queries for example to get all my "Admits", I'd rather query another table that has the following columns as bits: Admit Accept Withdraw That way I can query the admit column and get all my admits. How can I use SSIS to transform my "Decision" column into those bit columns? Thanks for any help or suggestions you have.
Can someone pls help me with how to check for a numeric value in a varchar column?
For example I have a column called client_id , it has values "AB" , "CD" , "18", "19" . I need to delete those client_id where the values are 18 and 19. How would I do that?
In a stored procedure I have I have dates in the format YYYYMMDD with symbols representing the first 3 digits e.g. °30903 =20030903, and I have to convert them to proper dates, and then eliminate all old data, so I replace symbols and then convert to int
I am trying to query multiple columns for a specific value. I have 8 columns (values are either 1 or 0)and I want to query the table to find out which rows contain zero's in ALL of the 8 columns. Whats the best way to do this? I can create a lenghty select statement where column1 =0 and column2 =0 and column3=0 and column4 =0 and column5 =0 .... etc. I was wondering if there was an easier way to do this?
I have 5 to six id columns from my excel input.they can be empty in some cases but of the five columns atleaset one column should have some value....All the five columns should not be empty.
Can you tell me if this is possible? (and how to do it!!)
The application is VS2005, with sql database.
I want to check if a specific column exists in a specific table in the database and if not then add it, all via my application.
I'm happy knowing how to connect to the database & pass sql commands (as I'm doing that anyway to set off backups), but not the actual queries I'd need.
Well, actually, as in the title. I have a table. The script should add a column if that column doesn't exist already. I use VB to combine the two queries. So what I want:
I have a table in my database and the table has almost 45 columns and the rowsize is 10468 bytes.in that most of the colums have varchar datatypes and and i think coz of poor knowledge of the data most of the columns with varchar data were given more column length. Now i want to decrease the size of those columns and to see the row size would be around 8k Bytes.If i do this now, does it affect the table performance much....Infact can i do this as there is lot of data (almost 2 million rows) in the table.If it is possible is there anything to be taken care before changing the column lenghts.
