SET @Counter = 1
SET @Max = (SELECT Max(DTect_Supplier_SRN) FROM SUPPLIER_TABLE_TEST)
WHILE @Counter <@Max
BEGIN
SET @MATCH_Supplier_name = (SELECT Match_Supplier_Name FROM SUPPLIER_TABLE_TEST WHERE @Counter = DTect_Supplier_SRN)
SET @Index = LEN(@MATCH_Supplier_name)
WHILE @Index > = 1
SET @MATCH_Supplier_name = CASE
WHEN SUBSTRING(@MATCH_Supplier_name, @Index, 1) LIKE '[a-zA-Z]' TH EN SUBSTRING(@MATCH_Supplier_name, @Index, 1)
WHEN SUBSTRING(@MATCH_Supplier_name, @Index, 1) LIKE '[0-9]' THEN SUBSTRING(@MATCH_Supplier_name, @Index, 1)
ELSE ''
END + @MATCH_Supplier_name
SET @Index = @Index - 1
--PRINT @MATCH_Supplier_name
SET @Counter = @Counter + 1
END
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?
Hi. In our database, we have a Social Security Number field. We've made application upgrades and we can no longer have the dashes ( - ) between the numbers. So, I ran this update on our database to remove all the dashes. it did remove all the dashes except it put spaces in its spot:
UPDATE DefendantCase SET SSN = REPLACE(SSN, '-','')
so, i tried this query and it does nothing.
UPDATE DefendantCase SET SSN = REPLACE(SSN, ' ','')
As you can see, there is a lot of spaces in here. The reportviewer removes these spaces. so that it look like OEM Part Code Part Code Part Description"
Instead of OEM Part Code Part Code Part Description
Why is it doing this? Can I stop it from doing this?
I guess there is no built in functions to do this but I have a function that replaces anything that is not A-Z with a space and returns @data. What I additionally need the function to do is scrunch up @data (remove all blanks betwwen each word so that 'I ran very fast' would be 'Iranveryfast').
What I need help in doing is the "Scrunch" part. Is there a way I could move the @Data to something like @DataHold and inspect each character, if it is not a blank, move that character back to @Data? This was pretty easy for me to do in C# with a while loop, but I do not know how to get it done in SQL Server 2005.
I have three columns, RecordID, FirstName, and LastName, but somehowthrough some program glitch, there is sometimes a trailing space inthe firstname and lastname columns, for example, a persons name couldbe entered as "John " "Smith" or "Bob " "Johnson "I know there is a RTRIM function in sql, but the problem I/m having ismaking an update line go through each row, and removing trailingspaces on those two columns. Any help will be greatly appreciated.Thanks in advance.
Hello,This is a simple question, hopefully with a simple answer. I havean nvarchar column of length 255. In one of the rows I have thefollowing sentance - 'See the brown ball bounce'. Is it possible touse a command to remove all of the spaces in that sentance, so thatthe sentance reads 'Seethebrownballbounce'? As you can see, I am notjust interested in getting rid of the trailing and leading spaces.Thanks,Billy
I have a table . It has a nullable column called AccountNumber, whichis of varchar type. The AccountNumber is alpha-numeric. I want to takedata from this table and process it for my application. Before doingthat I would like to filter out duplicate AccountNumbers. I get most ofthe duplicates filtered out by using this query:select * from customerswhere AccountNumber NOT IN (select AccountNumber from customers whereAccountNumber <> '' group by AccountNumber having count(AccountNumber)[color=blue]> 1)[/color]But there are few duplicate entries where the actual AccountNumber issame, but there is a trailing space in first one, and hence thisduplicate records are not getting filtered out. e.g"abc123<white-space>" and "abc123" are considered two different entriesby above query.I ran a query like :update customers set AccountNumber = LTRIM(RTRIM(AccountNumber)But even after this query, the trailing space remains, and I am notable to filter out those entries.Am I missing anything here? Can somebody help me in making sure Ifilter out all duplicate entries ?Thanks,Rad
Good day I've looked for information about this but to no avail. Essentially why does Reporting Services not support spaces and special characters in a Field Name? I know that SQL supports spaces utilizing [] brackets e.g. [Field Name With Spaces]. So how come RS doesn't? Thanks
I wanted to remove duplicate records from SSRS report. I set the "Hide Duplicates" to True. It is now working, But i am getting the space between the two records, which i want to get rid of. How to get rid of extra spaces between two records ( Please find the details below).
I have a table named users. It consists of user names, user ids, etc... The problem is that whoever designed the ASP code before me allowed people to enter info in any format they want. This poses a problem because now the name can have 1, 2, or sometimes 3 spaces in between the last and first names. And sometimes the middle initial is used with a period following it. This creates problems when I am trying to execute a Select statement since it won't match if there are an unknown number of spaces in the string and throws an error when a period is used.
Is there a SQL query I can execute to change all the user_names into a format such as the following: LastName, FirstName MiddleInitial
Like I said, it is already almost the same, just has too many spaces and some have periods after the middle initial
I have a table called exchange and field called address. The rows(1400+) in the field look like: MS:VA/Celcmv/VHACLEADAM%SMTP:Doe.Jane@med.va.gov%X200:c=US;a= ;p=av;o=Celcmv;s=Doe;g=Jane;
How do I remove everything to the left of doe.jane@med.va.gov and everything to the right of doe.jane@med.va.gov using query analyzer? Thank you in advance...
Can someone please suggest a function to remove the last 3 characters from a column? I was thinking of the LEN function, but I am unsure of the syntax.
I've got one SQL Server 7.0 table with a "Decsciption" Column of length 4000. The values in this column contains "End of Line" ASCII Character. The ASCII Value of this character is 10. I'm not able to remove this ASCII Character. I tried by using REPLACE function. But i could not remove that character.
Folks ... I have a pipe-delimited ASCII text file with a lot ofdifferent non-printing characters. Rather than try and figure out allthe non-printing characters that exist in this 17+ million recorddatabase, I was hoping someone might have already written a scriptthey'd be willing to share that would remove all non-printingcharacters from an ASCII file?Thanks,Ralph NobleJoin Bytes!
I am using the following expression to strip the last 11 characters in a field group and it returns an error saying that Len cannot use a negative number. It must be 0 or greater. Is there a better alternative?
I was wondering what would be the best way to remove special characters like, '-', '&' '(',')','#','*', etc... from a number string. To be specific a phone Number string where the string is >= 10.
I have a table with several columns of information that I wish to set up some form of schedule to go through this data and remove any special characters that may interfere with other code processes.
Mainly the coma's and the apostrophes. It really messes with my asp pages and scripts when retrieving this information and trying to do other things with it, so I need to figure out how to remove these from the tables so it does not cause these issues.
Knowing this, I cannot figure out how to keep the data in the row/column and just extract the special characters from that data. The other problem is, everything I try requires me to insert either a coma or apostrophe as part of the code string which in lies my issue.
How can I parse through my data, leave the data as-is, but just get rid of coma's, apostrophes, and double quotes?
Does anyone have a basic example that I can use to expand on?
I usually do this through Access so I'm not too familiar with the string functions in SQL. My question is, how do you remove characters from the middle of a string?
Ex: String value is 10 characters long. The string value is X000001250. The end result should look like, X1250.
I've tried mixing/matching multiple string functions with no success. The only solution I have come up with removes ALL of the zeros, including the tailing zero. The goal is to only remove the consecutive zeroes in the middle of the string.
I have a varchar field which contains some Greek characters (α, β, γ, etc...) among the regular Latin characters. I need to replace these characters with a word (alpha, beta, gamma etc...). When I try to do this, I find that it is also replacing some of the Latin characters.
hi , can anyone tell me if there exists a function in SQL Server that help me determine if a variable is ALL Alphanumeric data or not by returning a 0 or 1 when condition fails or succeeds . thank you
Our company needs to (restore/migrate) our Alpha Nt4sp4 sql 6.5sp5a databases to an Intel hardware platform. For the time being the new intel server will be runing Nt4sp4 sql 6.5 sp5a. I understand that if we were to upgrade to sql 7 we would be able to restore the databases without any problems. However upgrading to sql 7 is not an option right now. I have tried to restore a backup from the alpha to an intel based machine, and it fails because the processor types are different.
I have already search technet, but found nothing, except upgrade to sql 7.
I am hoping someone out there could point me in the right direction.
Thank inadvance for your help, I appreciate it very much.
For the last couple of days i've been trying to transfer a SQL6.5 database from an Alpha platform to an Intel one.
At one point i loaded SQL7 on the Intel platform and tried to perform an upgrade (using the wizard), across the network - but it just did not want to play.
Using Veritas Backup Exec, with SQL6.5 on both machines, i've tried doing a backup of the database on the Alpha platform and then restore to the Intel Platform, but this fails stating that the processors are incompatible
Am i trying something that is impossible?
If anyone could point me in the right direction i'd be truely grateful.
I am looking for ways to migrate Sqlserver 6.5 from Alpha to Intel plat form. The only method I heard was Database/Object transfer. Is there any other way?