I need to strip out all alpha chars and spaces in a given field and return only the numbers.
I've tried =CInt(Fields!Info.Value) and get an unexplained error. If the data was formatted consitantly I could simply do a RTrim or Right, but the number strings are not the same, some have spaces as in phone numbers (1 800 555 1212) or don't have a leading 1. Most instances are correct for my purpose (8005551212).
Any help would be appreciated.
UPDATE: Using the Replace function =Replace(Fields!Info.Value, " ","") gets me almost there. Now I should be able to use a Right, 10 function to return my desired value. Is it possible to combine these two funtions together?
I have a field name call firstname and other field middleinit, now the table contains data. and i want to extract data, the firstname field sometimes contains the middle initial, for for example JOHH D. now I would like to set the firstname field with JOHN only and and set the middleinit field like D only thanks
Hi, I have a little bit of a problem I cannot seem to figure it out. Is it possible to write a Select statement that contains a WHERE column_name > desired_numeric_value The tricky part it that the column is of CHAR type and can contain numeric grades ranging from 0-100 or the letter I for Incomplete. My SQL was working perfect when this column contained only numbers as soon as a record with I was added I get the following error: Character to numeric conversion error This report will be used to find students who have failing grades. Thanks for any help!
In a query Analyser, there is a provision to change the "Maximum characters per column 256 " to our size in advanced option.
It works ok.
We are using VisualInterdev for developing ASP's -- there thro ADO - we are getting the rows from sql server 7.0 -- there data more than 256 characters are truncated.
How will i get the actual data ?.. where i can do the settings..?
I am creating a report query that returns all unreconciled P/O lines. I am near completion but I am unable to find a way to remove the reconciled records.
I have included a script to produce some sample table, data & query.
The recordset dispalys 6 rows. All reconciled Supplier Invoices are duplicated and have transaction codes 40, 50 and reconcile code of 9 (5024, 921689471).
All unreconciled only appear once and have transaction codes 40 and reconcile code of 0 (4835 & 921978016). These are the only records that I want to show.
I am having DTS problem exporting from a table to a text file (my 600 character table column is truncated to 255 characters in the output text file). I don't why this is happining nor how to get the DTS not truncate to 255 characters. Is this a bug? A limitation? How do I get around this problem? .......I am running SQL Server 7 SP1 and database is in 6.5 compatability mode.
I have a SQL 2012 database that has 10 tables. One of the tables is populated by manual import from CSV file. Each time a user calls custom ASP.NET code., records get inserted into a table called forecast_data with incremental increase in FileID. So first import has FileID of 1, second import has FileID of 2 etc.
What I am trying to do is only keep the data that has the highest FileID (MAX(FileID). I would like to write a store procedure that removes all older data once a new import is written into the table.
In QA we have a two-node cluster with four instances of sql. In trying to add a fifth, I was given an IP address already in use so the install hung.
I removed it from the cluster but it is still there in the registry etc on the node I was working on.
I read about using the maintenance tab of the sql server install to "remove a node" but the terminology is confusing. To me a node is a physical server and an instance is an instance of sql server -- not the same at all but they are often referred to as the same thing.
I definitely don't want to remove one of the servers from the cluster.
BEGIN TRANSACTION Copy records from live to archive END TRANSACTION with commit or rollback execute sproc to write audit log with success or fail IF transaction was committed BEGIN TRANSACTION Delete records from live the archive END TRANSACTION with commit or rollback execute sproc to write audit log with success or fail End IF
END TRANSACTION OUTERTXN with commit if both inner transactions were successful or rollback if either failed
If either inner transaction rolled back execute sproc to write audit log saying whole process is rolling back End IfMy problem is that if the outer transaction rolls back then I am losing the two audit records because they are part of the transaction scope. I want these executes to commit even if the master transaction fails.
Hi;I am trying to write a rountine ( below ) that will go into a colum oftext data type ( fae.pmcommnt ) locate the word "to" and replace it.I have the routine below. I get no error messages, but it also seemsto do nothing :).Any clues would be greatly appreciated.ThanksSteve================================================== =============declare @ptrP intSELECT @ptrP = PATINDEX('%to%', pmcommnt)from fae where projid ='00013'declare @ptrPC binary(16)select @ptrPC = TEXTPTR(pmcommnt)from faeif( TEXTVALID ('fae.pmcommnt', @ptrPC ) > 0 )print 'works'print @ptrPUPDATETEXT fae.pmcommnt @ptrPC @ptrP 2 'JJ'select projid, pmcommnt from fae
I receive the error " [OLE DB Destination [1907]] Warning: Truncation may occur due to inserting data from data flow column "GENDER" with a length of 255 to database column "GENDER" with a length of 2. " on the source flow.
I have done some googling and came up with this post: http://torontosql.dotnetnuke-portal.com/Default.aspx?tabid=32233 which I thought may help, but it does not.
The query against the access datasource features the column: iif([sex]=1, 'm', 'f'). I tried using left(..., 2) as well, but SSIS is determind to treat the field as 255 characters for some reason.
I don't even particualrly care that the field is 255 chars and the sources is only two, I just want the data in! I have other fields coming up with similar error.
Can someone please advise?
PS, what is th significance of the "External Columns" Vs "Output Columns" on the Input and Output Properties tab in Advanced Editor?
I am really struggling with SSIS, it is not as intuitive as DTS.
I have an Address column that I need to Substring. I want to remove part of the string after either, or both of the following characters i.e ',' OR '*'
Example Record 1. Elland **REQUIRES BOOKING IN*** Example Record 2. Theale, Nr Reading, Berkshire Example Record 3. Stockport
How do I achieve this in a CASE Statement?
The following two case statements return the correct results, but I some how need to combine them into a single Statement?
,LEFT(Address ,CASE WHEN CHARINDEX(',',Address) =0 THEN LEN(Address ) ELSE CHARINDEX(',' ,Address ) -1 END) AS 'Town Test'
,LEFT(Address ,CASE WHEN CHARINDEX('*',Address ) =0 THEN LEN(Address) ELSE CHARINDEX('*' ,Address ) -1 END) AS 'Town Test2'
I got a sales cost and cost amount table for my budget. the sales cost table is getting updated with FOBB items which makes the total incorrect . the FOBB values needs to be moved from the sales cost column to the cost amount column. how can i do it with an SQL script.
I tried to remove AdventureWorksDB in the "Add or Remove Programs" of Contol Panel and I got the following errors: (1) AdventureWorksDB Error 1326: Error getting file security: CProgram FilesMicrosoft SQL ServerMSSQL1MSSQLGetLastError: 5. |OK| and (2) Add or Remove Programs Fatal Error during installation (after I clicked the |OK| button). Please help and tell me how I can solve this problem.
I have uninstalled the CTP version of the SQL Server express so that I can install the released version but CTP version is still listed in the add/remove program list but without the change/remove button. I have been to different sites to find information on cleaning this up and I have ran all the uninstall tool I can find but the problem still prevails. I cannot install the released version without completely getting rid of the CTP version. Please help anyone.
We are using VB 6.0 as frontend and SQL server 7.0 as backend. We want to store some special charcters like Alpha, Gamma, etc in the database. We tried different ways but could not find the solution.