Removing Spaces From An Nvarchar Column
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, ' ','')
does anybody have any ideas? Thanks!
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.
Thanks for any help you can offer.
I have a text box with the following expression:
="OEM Part Code" + " " + "Part Code" +" " + "Part Description"
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"
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.
Thanks for any help!
For €śnvarchar€? column if we pass the value as spaces, then it stores as space.
declare @path nvarchar(50)
set @path = ' '
select '=>' + @path + '<='
Is there any setting / configuration that will force it to automatically trim the spaces and store it as null?
Thanks in Advance,
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.
I loaded data into a CHAR fields from Legacy.
How to remove those leading spaces with in SQL server...?
How do i eliminate records which has spaces
i tried with
select * from table
where col1 !='' or
col1 is not null
which doesn't work.. any help.
Hi I am trying to strip out any non-alpha characters from a field.
i.e. Field = ABC"_IT8*$ should return: ABCIT8
I am writing a loop to do this for all values of a field. The script runs, but hangs....please could somebody advise on the code below...:
I run the script but it doesn't seem to finish. Can anybody see any issues with the code:
DECLARE @Index SMALLINT,
-- @sqlstring varchar(500)
SET @Counter = 1
SET @Max = (SELECT Max(DTect_Supplier_SRN) FROM SUPPLIER_TABLE_TEST)
WHILE @Counter <@Max
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)
END + @MATCH_Supplier_name
SET @Index = @Index - 1
SET @Counter = @Counter + 1
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..
Any Suggestions would be appreciated
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:
CREATE FUNCTION [dbo].[PadRight]
@padCharacter NCHAR(1) = ' ',
@trimBeforePadding BIT = 1
RETURNS NVARCHAR(4000) AS
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)
I have a single column value like this. I want to get the charindex value whereever it is a single space.
'6 REP5426 15936 022708 107/0000 33003301985 BAILEY AMY NICOL 11454 PP 25.00 000-00 00110080 A O '
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 (' ')
, 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
Here is a snap of how I would like to set up another entry:
Is it a bad practice to create column names with spaces. like [Last Modified On]?
If yes, y?
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!!
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.
(I know distinct)
Thanks in advance.
I have a table called exchange and field called address. The rows(1400+) in the field look like:
How do I remove everything to the left of firstname.lastname@example.org and everything to the right of email@example.com using query analyzer? Thank you in advance...
How to remove identity property from a column throught SQL statement?
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 want to remove text from my column name using query.
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?
We can easily remove identity columnn through enterprise manager but how can it be done through transact sql?
The only way i found is to create a new column and pass values if identity column in it and then remove this identity column is there any better method of doing it?
I have the following tables :
Create table #EmployeeList(empname nvarchar(20), emptype char(5))
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
Expected Output :
Can anyone help me please with the query?
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
Assuming that I have a table similar to this simplified version:
CREATE TABLE [SomeTable] ([Field1] NVARCHAR(50));
How could I count and rank the instances of specific values in the "field1" column? For example, let's say that I want to show the frequency of the column values like so:
some value 24
another word 20
a value 5
(and so on...)
How can I do this?
- - - -
- Will -
- - - -