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
I'm trying to create a case function for home phone ,work phone and cell phone. The thing is some of the home phone numbers either null, zero or less than 10 digits then i'd like to get either cell phone or work phone if they are not null, zero or less than 10 digits.
I have a column of phone numbers that have formatting such as (xxx)xxx-xxxx, or xxx.xxx.xxxx or xxx.xxx-xxxx; however, not all phone numbers are formatted in this fashion. I need to remove all characters and only leave behind numeric digits to look like xxxxxxxxxx. I have tried select replace(colname,'-','') from table. This does the trick but does not actually change the values in the column. Any help in accomplishing this would be helpful. Thanks in advance.
Hello, I have a phone number field with the format (123)-456-7890 I need to convert this to 1234567890 formats while I am retrieving data from the table. How can I do this?
I need to formate one of my phone number field in SQL. now the data in the field is: 1234567890. I would like to formate the number to 123-456-7890. Does anyone know how to do this? Thanks.
I need to strip some puntcuation from any field in a given table.I'd rather like to avoid using the replace () for each field in thetable.Anyone have a nifty way do this?Is there a special name that I can use in the replace that means theentire row?(other than syntax, something like REPLACE(@ROW,CHAR(39),'') )tiaRob
Hi there - I would like to share this strip of code with our SQL 2000DBA community. The code below strips all non-numeric characters from agiven string field and rebuilds the string. Very simple, but I had tobuild it from scratch due the lack of info on this specific matter. Iam sure there are better solutions out there, although I will be gladif this script can help anyone. Feel free to modify and comment itback.Regards,Rubem Linn JuniorMCSE, .NET developerWeb Apps Specialist------------------------------------------------------- BEGIN---------------------------------------------------DECLARE @String_Length AS INTEGER -- Length of the given stringDECLARE @Original_String as NVARCHAR(50) -- The field to stripnon-numeric charsDECLARE @counter as integer -- simple counter variableDECLARE @Stripped_String as nvarchar(50) -- The field after beenstripped-- Get the length of the field (string) to be parsedSELECT @String_Length = len(someStringField) FROM SomeTable WHEREFilterID = 001-- Get the field (string) to be parsedSELECT @Original_String = someStringField FROM SomeTable WHEREFilterID = 001-- Set counter variable to 1SELECT @counter = 1-- Reset this variableSELECT @Stripped_String = ''-- Initiate loop from 1 to the Length of the given stringWHILE (@counter) <= @String_LengthBEGIN-- Check if the char in the lap is numericif substring(@Original_String,@counter,1) LIKE '[0-9]'BEGIN-- Load this variable with the non-numeric-- data stripped from the original stringselect @Stripped_String = @Stripped_String +substring(@Original_String,@counter,1)END-- Increment the counter by oneselect @counter = @counter + 1END-- Print the original string with all charactersPRINT @Original_String-- Print the numeric data that was stripped outPRINT RTRIM(LTRIM(@Stripped_String))
I have a table in SQL Server 2000 which has few triggers. When I try to update a record in that table, I get following message:
Warning: The table '[TABLE_NAME]' has been created but its maximum row size (17275) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
where the data I am updating is well less than the field limit. The triggers are stored in syscomments table and when I dropped these triggers, the update statements were executed without this message. Is there anyway I can change the data type of field text in syscomments from nvarchar to ntext?
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.
Is there any way to remove the My Subscription,Site Setting links on the main page in Report manager.And also our client wants SSRS reports to be seen by his customer over the internet.Anybody knows about this? Any help will be appreciated.
I get this error message when I try to connect to Reporting Services via the Management Studio.
I can see my machine listed in the Server Name > Browse For More > Local Servers dialogue. But no luck,
Ive tried:
Servername: localhost Servername: DED1774 (the machine name) Servername: localhost/reportserver Servername: DED1774/reportserver Servername: http://ded1774/reportserver (from the rsreportserver.config file
<UrlRoot>http://ded1774/reportserver</UrlRoot>)
I've Googled the error message and found postings for solutions, but none of these helped. Can anyone suggest some simple steps I can take to try to find the issue and get the connection working?
I have a field that contains some text. Each field will have none or atleast one comment in it. A comment can be a string of any length with *** on both sides. Ex: ***comment***
Declare @Test_tbl Table(TextField Text) Insert Into @Test_tbl Select 'Some text ***comment*** some more text' Union all Select 'Other text ***another comment*** more and more text' Union all Select '***Comment*** some text ***More Comments***' Union all Select 'some text with no comment'
I need the output be ...
TextField -------------- Some text some more text Other text more and more text some text some text with no comment
I have two linked tables from two different databases, there is a column "product" on each table however the product on one table has a Prefix so not a direct match. How can I join these tables ? In the query I have used product2: Replace([scheme_pos.product],"-B","") then tried Joining on product2 but it says JOIN not supported.
I am trying to write a user defined function that will allow me tostrip off the last carriage return and line feed from a text field.We have address fields stored in a text field for our ERP system andsome of them have an extra carriage return and line feed at the end ofthem. This causes havoc when we sync between our ERP system and CRMsystem. If anyone knows a way to solve this problem the help would beappreciated.Examples:Existing Text field with CR:1234 Blah Street<CR>Suite 2345<CR>Corrected Text field:1234 Blah Street<CR>Suitr 2345
I have a 'notes' field. Entries in there are time-stamped as well. so lines looks sort of below... 01/01/2001 10:00 am Called him. Sent him some info. 03/01/2001 2:00 pm Nice guy. Fun talking etc
Is there a SQL function/way by which I can grab entries from 'notes' field by date ?
i am having a problem. In my table i have defined a logtime field as a datatype datetime. Now i want to query the table on a particular date which is not possilbe i supposed. so how can i break the datetime field into date field. Is there any method.... I don't want to create the table once again.
I need to expand the size of one column which has been defined as varchar(32) to varchar(50).Is this possible?Already there are many old records in the table,in what way it will effect the old records?Any help is appreciated. Thanks!!