This is a simple question, hopefully with a simple answer. I have
an nvarchar column of length 255. In one of the rows I have the
following sentance - 'See the brown ball bounce'. Is it possible to
use a command to remove all of the spaces in that sentance, so that
the sentance reads 'Seethebrownballbounce'? As you can see, I am not
just interested in getting rid of the trailing and leading spaces.
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
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, ' ','')
I have a table of addresses I need to import each month and under the 'County' Column the word County or Burrough follows each county name. Ex 'Baltimore County', 'Washington County', 'Davis Burrough' etc.
Using DTS is there a way to remove the space and County(Burrough) following each county name? I've tried using the trim feature but that only seems the Trim the White Space.
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.
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 have a main report that does some row groupings.. When there is little data coming out, there tends to be a widish gap between main report and sub report..
I tried placing the sub-report in the table footer but this landed up squashing the column width's of the sub report..
Is there another way to get this right ? I tried placing the sub report into a rectangle, and the main table into its own rectangle and then merging those into another rectangle.. This unfortunately did not work..
I'm trying desparately to write a PadRight function in SQL Server 2005. I seem to be failing miserably because the trailing spaces disappear when the data is returned. First of all, why does SQL Server think I want my string trimmed? And second, how do I overcome this? Code below:
Code Snippet CREATE FUNCTION [dbo].[PadRight] (
@sourceString NVARCHAR(4000), @length INT, @padCharacter NCHAR(1) = ' ', @trimBeforePadding BIT = 1 ) RETURNS NVARCHAR(4000) AS BEGIN
DECLARE @returnStringLength AS INT, @toReturn AS NVARCHAR(4000) SET @toReturn = LEFT(@sourceString, @length)
IF @trimBeforePadding = 1
SET @toReturn = RTRIM(LTRIM(@toReturn)) SET @returnStringLength = LEN(@toReturn) IF @returnStringLength < @length
SET @toReturn = @toReturn + REPLICATE(@padCharacter, @length - @returnStringLength) RETURN @toReturn END GO
i'm going nuts with this, i suppose i will crack it eventually, but i thought i'd ask around here, seems like all the smart SQL Server guys hang out here
(i'm an SQL guy, not an SQL Server guy)
how does one place 5 spaces into a CHAR(5) column? create table testzeros ( id smallint not null primary key identity , myfield char(5) ) insert into testzeros (myfield) values (' 1') insert into testzeros (myfield) values (' 11') insert into testzeros (myfield) values (' 111') insert into testzeros (myfield) values (' 1111') insert into testzeros (myfield) values ('11111') insert into testzeros (myfield) values (' ')
select id , myfield , len(myfield) as L from testzerosno matter what i do, id=6 shows up with L=0, just like an empty string
i've even tried inserting 4 spaces and a non-blank character, which enters just fine, just as you would expect, but when i update the value and replace the non-blank character with a blank, all 5 spaces collapse back to an empty string
is there some kind of server setting like SET ALL_SPACE_EQUALS_EMPTY_YOU_IDIOT to OFF or something?
I am fairly new to SQL I have started to administer a system which handles carrier information for a mail order system. The logic behind the system is quite simple there are 5 or so columns in a table the first column is the first part of the postcode i.e EX15, the other columns contain which delivery services and depot numbers are associated with that postcode. It works fine at the moment.
However now the main carrier has decided that they are now going to split these postcodes so for example EX15 1* goes to a different depot than EX15 2*
I cant seem to insert EX15 1 into the first column, I get the following error:
Attempt to store duplicate value in unique column. (-155)
Is this because of the space in 'EX15 1'? Because 'EX15' already exists in that column? In which case do I need to somehow tell SQL that there is a space there?
I hope this makes sense
Below is a snap of the table with the existing EX15 postcode data
Hi all,I am new to these so plz never mind if this is funny.here is my problem :Table : moodyColumn : TitleNew column : NospaceI have data in "Title" column of many rows which are normal sentence.My requirment is to remove the "white space", +, | , ., / , ! @, $, %etc special characters and fill it by ( hyphen) and put it in new"Nospace" ColumnExample :I have : Hurray ! I won the GameNeeded : Hurray-I-won-the-GameCan any body helpme in getting an SQL Query for this if possibleThanks in Advance
I currently have a column in a table with data type char and length 500. However, not every column fills up the entire 500 length, and I would like to fill up the rest of the spaces with dots. Is there a setting in SQL to do this? I do not want to use varchar since I want a fixed length with dots at the end. Any ideas?
I am using the following command master..xp_cmdshell 'bcp Staging.dbo.TableA format nul -f "\CommonTableA.xml" -x -r"" -c -t -T'
to generate a XML format file so that I could later use during import process and the structure of the table is CREATE TABLE TableA ( [First Name] AS VARCHAR(50), LastName as VARCHAR(50) )
it fails, it throws Error = [Microsoft][SQL Native Client]Format File : Invalid Field index. When I change the column name by removing space it works. Is there a work around for it, the reason being the Tables and format files has to be generated on the fly as its a requirement and I want to use the XML format instead of FMT type.
I am using Advantage ODBC 6.2 to connect to a Advantage Data Server to push data from a SQL table into this server. I can view the data from the ADS with DTS and I can insert data by using "Insert into TempTable ("Last Name","First Name") values ('test','test)" from a Execute SQL Statement. The issue is when I build up a DTS package to pull from SQL into the Advantage ODBC, I get a "missing closing ")"" error. I have narrowed it down to the column names in the destination table having spaces (Last Name, First Name, and a bunch of others. DBA 101 here). How do I beat this?
I have tried editing the destination column names in disconnected edit by adding the double quotes but get a "Column Name '"Last Name"' not found" error. Wrapping them with  doesn't work. I think this is a limit on Advantage.
When I try to use Advantage OLE DB, I get an 'Ace32.dll must be newer then the other dll" error. I am afraid of upgrading Ace32.dll and not break other things.
So with this said my two questions:
How do I get the destniation column names wrapped in double quotes when using transformations?
Is there a way I can do an Insert into Advantage connection(column names) values (Select Values from SQL Server connection)?
I have to send a field delimited file which is exported from our SQL server to another agency. The format is very strict. It has to be 57 chars for the total record with CR/LF at the end of every column. There can be no exceptions.
In the past, I did a normal fixed width export from SQL Server 2000 and chose the end of line markers as CR/LF.
We have newly upgraded to SQL Server 2005 and everything is workign great except my simple DTS file export. With the DTS changes, a normal fixed width export will not put the CR/LF at the end of the rows which means that they reject our file. That means I have to do a ragged right export. When I do this, it trims the trailing spaces out of my last column before adding the CR/LF to the end or each row. If it is supposed to be fixed width, why is this trimming my trailing spaces off of there? You would think that it would leave my last column as a width of 5 regardless of spaces. Those trailing spaces must be there for the file to work properly.
This makes absolutely no sense to me. Perhaps someone can shed some light on this or explain me what I am doing wrong.
My company is starting to use nvarchar columns in our database products. We just found out that, suppose table T1 has a my_nvarchar_col column, and there is a row containing a unicode text say "some Chinese", if you want to select that row, you have to append "N" in front of the unicode constant in the "WHERE" clause. That is:
select * from T1 where my_nvarchar_col = N'some Chinese'
will return that row, while
select * from T1 where my_nvarchar_col = 'some Chinese'
will return NOTHING.
This brings us a huge problem - we have tens of thousands of such queries in our existing PowerBuilder code base. Do we have to go through all of them to add "N" to the "WHERE" clause? Is there a way we can set some attribute of SQL Server so that we do not need to do that?
Project stalled and I am under extreme pressure so please help ASAP!!
Hi all, I have one table in which one column contains duplicate values. My question is how i can use T-SQL so that i can retrive values for all columns in the table which are distinct and retriving the single value from column which contains duplicate values.
Hi all i want to remove text from my column name using query. for example i have the product name like "silver 8' trampoline pack " i need to remove "silver 8' " and want to display only trampoline pack similarly if I have product name like "gold 8' trampoline pack" i need to display only trampoline pack. can anybody help me in this regard?
Insert INTO #EmployeeList('Cary zzz',null); Insert INTO #EmployeeList('01 Jack',null); Insert INTO #EmployeeList('02 Tommy',null); Insert INTO #EmployeeList('03 Ricardo',null); Insert INTO #EmployeeList('04 Jack',null); Insert INTO #EmployeeList('Les zzz',null); Insert INTO #EmployeeList('05 Tim',null);
The final data looks like this :
Cary zzz NULL 01 Jack NULL 02 Tommy NULL 03 Ricardo NULL 04 Jack NULL Les zzz NULL 05 Tim NULL
1. I want to delete all rows which have 'zzz' in it. 2. I want to remove the numbers from the empname column
Code Block Expected Output :
Jack NULL Tommy NULL Ricardo NULL Jack NULL Tim NULL
Users can approach their userprofile on my site using: www.mysite.com/name=peterName is a unique value within my database (db type: nvarchar(50))Now, I have created a clustered index on the username column.However, IMHO its faster to create a clustered index on the (also unique) usercode column since that is of type int.BUT since a user can approach my site based on username I feel that I HAVE to live with this setback in performance....Is that true or is there a better way to solve this issue?
Hi, I want to add a newline in a content of sql column (using t-sql and not asp.net textbox) so when content is being rendered in a .net textbox I get separate rows, so insteadcol1 col2 col3 I woule like to havecol1col2col3Thanks