Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server & have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for

How To Remove Blank Spaces In Records??

We imported approximately 2.9 million records from our mainframe server
into our SQL Server but have run into a problem.  The data in a
few of the fields contains both leading and trailing spaces.  An
example of the data would be like this, using periods to represent

What we have:


What we need:

1A02938  (no spaces)

 Is there some sort of algorithm I can run on the data to remove
those spaces?  The problem is coming up when trying to perform a
SELECT query.  We try something like:

SELECT * FROM PCPIPT0 WHERE PANO20 = "1A02938"  but we get zero
results because of the spaces in the database.  The datatype of
the filed is char(20) because we need some flexibility on the size of
the data stored.

Any assistance would be greatly appreciated.

View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
How To Remove The Blank Spaces Between Subreports In Sql Server Reporting Service?
In Sql server reporting service the blank spaces or white spaces are coming in between the subreports, when we place the subreports in the main reports.

If any one know how to remove the blank spaces between the subreports, please reply me. Its very urgent.

View Replies !   View Related
Replace Blank Spaces
i have a field with blank spaces.
i wanna replace the spaces with just one spaces. ihave 500 fields in 500 tables.

any input will be appreacited.

i have something like this but its not working.

declare @field varchar(50)
declare @minVoter int
declare @maxVoter int
declare @tableName varchar(20)

set @tablename = '00001'

-- select ad_str1 from [00170]

select @minVoter = min(id_voter),
@maxVoter = max(id_voter) from quotename(@tablename)

while (@minVoter <= @maxVoter)
select @field = ad_str1
from quotename(@tablename)
where id_voter = @MinVoter

update [00170]
set ad_str1 = replace(@field, ' ', ' ')

select @minVoter = min(id_voter)
from quotename(@tablename)
where id_voter > @minvoter


View Replies !   View Related
Varchar And Blank Spaces In SQL DB Field
I am trying to load a field in my DB and it is defined as varchar(11) but when I populate it, it still adds spaces at the end. When I try to use it in an If statement, it doesn't match and executes the else instead. The wierd part is it seems to make it 10 characters long and not 11 or the the actual length. I think I had originally set it up for char(10) then changed it afterward but I even deleted the field and reentered it as varchar(11).Thanks,Eric

View Replies !   View Related
Avoid Blank Spaces In Grouping Subreports

 In Main Report i got Several subreports.when i kept the subreport in grouping.blankspaces coming in between .i tried rectangle and adjusted page height and width nothing turned out.Pls help me.



View Replies !   View Related
Remove Spaces
How can you remove spaces in the middle of a string, RTRIM and LTRIM does not work

View Replies !   View Related
Remove Spaces
is there a way to do remove spaces from a string if its they are in deifferent places on each row

View Replies !   View Related
Replace Nulls With Blank Spaces In Float Data Type
I have a simple question. Is it at all possible to replace columns which has nulls with blank spaces for a float data type column.
The columns has null values( written)) in it in some rows and has numbers in other rows . I want to remove nulls before copying it to another file.

View Replies !   View Related
Need Your Help To Remove Spaces In The Column Entries Using SQL
Hi all,I am new to these so plz never mind if this is 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

View Replies !   View Related
Remove Leading Zeroes And Keep Spaces
I have a char(12) field that was loaded like '000000000101' I need to change the data to be ' 101'. Is there a way to do this and preserve the number and keep the leading spaces?

View Replies !   View Related
How To Remove White Spaces Between Words
What is the Select statement to remove white spaces between words?

View Replies !   View Related
Need To Remove Extra Spaces In Query Output
I'm using the following command:

osql -E -n -d testdb -i testquery.qry -o "c:Scriptsoutput.txt" -h-1 -w 500 -s ","

With the following query (testquery.qry):

SELECT table1.column5, table2.column9
FROM table1, table2
WHERE table1.column4 = table2.column4
AND table1.column1 != "NULL"
ORDER BY table1.column5

All of the columns are cast as char up to 50 characters.

Even if only a field has a few characters, I get a lot of extra white space in my output. I want to get rid of those trailing spaces. I've tried SET ANSI_PADDING OFF, RTRIM(), and CAST(x AS VARCHAR(y)). I still get the same output. What am I doing wrong, what am I missing?

View Replies !   View Related
Remove Alpha Chars &&amp; All Spaces In Field [UPDATED]


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?

View Replies !   View Related
Create TRIGGER Remove White Spaces From A Fields In Table-scan And Fix
i have table i use it for update insert
and the users use this table from a grid on the web
and i need to prevent from white space in the fields in table
so how to
create TRIGGER remove white space from a fields in table scan and fix it ?


Code Snippet
SELECT  TRIM(fieldname)
,            LTRIM(fieldname)
,            RTRIM(fieldname)
,            LTRIM(RTRIM(fieldname))
FROM     tablename

Code Snippet
WHERE     (LTRIM(RTRIM(fieldname)) = 'Approve')

Code Snippet
replace(@text,' ','')

create TRIGGER on update insert  and not to  damage the text in the all fields

View Replies !   View Related
How To Set &&"0&&" Instead Of Blank Spaces To A Column Or Field.
 Hi i hv a doubt in Sql server reporting..I do generate some reports based on  some criteria.In the results screen i hv empty fields based on the search i hv generated.I need to set "0" instead of blank spaces in the fields..Can any one help me?

View Replies !   View Related
Removing Records With Spaces In Fields
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.

View Replies !   View Related
Blank Spaces In Table Name, Cannot Delete Table
A table was created in version 6.5 that has 2 blank spaces and a slash '/' in it's name (i.e. Item Type w/Groups). This was done in error and now the table cannot be dropped or renamed and dropped.

Does anyone know how I can delete this table? Please let me know.

View Replies !   View Related
Show All Records If SQL Parameter Is Blank?
 Hi:I have written a SQL statement that accepts a letter and then prints out all the records in a table starting with that letter.  I was wondering if there is a way that I could change the query so that if prints out all records if a blank or empty value is passed in?Here's my query: ALTER PROCEDURE [dbo].[GetMediaListByFirstLetter] (  @firstLetter char(1))AS    SELECT Media_ID, OrgName        FROM         Media         WHERE UPPER(SUBSTRING(Media.OrgName,1,1)) = @firstLetterAny help doing this would be greatly appreciated.Roger 

View Replies !   View Related
Replacing Blank Records With Text
Hi there,

I'm in a bit of a jam here and will appreciate any help.

I need the SQL code to replace a record if the record is empty.

For instance, I have about 7 columns containing over 40K records. In the firstname field, some records are blank. I need to replace all the blank firstname fields with this: 'now invalid' (without the quotes)

What would be the best way to achieve this?



View Replies !   View Related
Find Records With A Blank Field
I want to be able to use a query to display all the records in the 6.5 database that have no data in the STATUS field. This is the query I thought would work....."SELECT * from travel_date WHERE status="''"

But, that is not working. Can someone please help me figure out the right way to wrtie this?

I appreciate your help!

View Replies !   View Related
SQL / Not Existing Records As Blank Cells

I have data in two tables.



I want to join the two tables to add the Code of CodeType "C" to the records of NAMES

Result Example

I want to have all records from the names with the codetype C, if there is no record with the codetype c for a given ID, the cell should be blank to identify for which ID's the CodeType C is mising.

how should the sql statement look like?

Please help!
thanks in advance!


View Replies !   View Related
How To Set A Parameter For Filtering Not Blank Records
In my report I want an optional parameter to filter all records with a specific field that is not blank. I tried several scenario's without result...

In the parameter I want to set a text value like "exampletext".

In the filter I want a check: if the parameter value is "exampletext", only show the records where field "abc" is not blank.

On the tab Filters from the Table properties I can set three values: Expression, Operator and Value.

Please help!

View Replies !   View Related
DTS Import From Excel To SQL7 Copies Blank Records
I'm importing data from excel8.0 to SQL7 with DTS package, but sometimes this procedure generates blank records. How does DTS determine when the end of record has been reached in excel or other sources?

Netica, Inc.

View Replies !   View Related
Need To Remove All Duplicate Records.

I have a data in one table like below.

---------- ------------ ----------------------
CNE TN-Town News 12/19/2007 12:00:00 AM
TN TN-Town News 12/19/2007 12:00:00 AM

What i have to do is if there are multiple records for one product in any day, then i need to remove all those records. In this case i am getting two records for the PRODUCT 'TN-Town News' and for INSERTDATE = 12/19/2007 . So i need to remove these two records from the table.

How to do that?. Can anybody help me?


View Replies !   View Related
How I Remove Records From The Log File
I have SQL Server Standart Edition and ż have a database, MDF file size is 650 MB, log file size is 1,23 GB

I tried to shrink the log file but i didnt shrink, I wanr to remove all records from the log file

How I remove records from the log file I want to shrink this file to 1 or 2 MB
Please help me

View Replies !   View Related
Remove Duplicate Records
hi, I have a table contains 3000 records, I ran this statement
select company_name,count(*) company_name
from vendor
group by company_name
having count(company_name)>1

This got me all companies and the duplicate counts, total
duplicate counts were 80. I need to remove the duplicate and
keep half of thoes companies...
how can I do so, please hlep


View Replies !   View Related
Remove Outlying Records...

Is there some Transformation or other method to remove outlying records based on an attribute during a Data Flow task?

I have a list of Organizations complete with a list of Products they have bought. I am going to do some data mining / profiling off of this data but first I need to get rid of the top 25% and bottom 25% quantity records by Product. I've looked at Percentage / Row Sampling but they are too simple.

How would this be done in SSIS?




View Replies !   View Related
Remove Duplicate Records From A Tble
hi, I run this script and found duplicate records. how can I delete all rows that have more than one record but Still keep one from each duplicated record


---------- -------- -------------- -----------
alb C0000322 3ma04 a12 0001 3
alb C0000398 13a04 a04 0001 2
alb C0000398 13a04 a04 0002 2
alb C0000398 13a04 a04 0003 2
I got 1717 row(s) duplicate, I need to keep only one record from each duplicate. so I can create a primary key on( SALES_CITY,ORDER_NO,CIRCUIT_ID )after I delete the duplicate.
thanks for your help

View Replies !   View Related
Remove And Archive Records Into Another Table
I currently have one table that lists all projects and tasks within the organisation.  One of the table fields is the task status, open or closed.  I would like to be able to have a process by which the tasks that are completed are removed from the table and placed into another (archive) table. The same records then being removed from the original table. which then only contains the incomplete tasks.  This process could be run at given times during the day or at the point when the status of a task is changed from open to closed, either way each time the process is run it would need to append the rows removed into the archive table. Anyone any ideas on the best way to do this?.

View Replies !   View Related
How To Remove Duplicate Records From Incoming Textfiles
Is there a way to check if duplicates exists in the incoming textfiles????


View Replies !   View Related
Duplicates Again! UNION Join - Remove Records With Column Diff.
Hello All,

We all were new at one point.... any help is appreciated.


Combining two 49,000 row tables and remove records where there is only 1 column difference. (keeping the specified column value removing the one with a blank.)


I have 2 people going through a list, coding a specific column with a single letter value. They both have different progress on each sheet. Hence I am trying to UNION them and have a result of their combined efforts without duplicates.

My progress/where I'm stuck:

Here is my first query/union:

SELECT * FROM [Eds table]
UNION SELECT * FROM [Vickis table];

As shown above, I have unioned these 2 tables and my results removed th obvious whole record duplicates, but since 1 column is different on these, a union without criteria considers them unique.....

an example of duplicates that I must remove are as follows:

142301 - Product 5000 - 150# - S (Keep)
142031 - Product 5000 - 150# - "" <--- Blank (Remove)

I am trying to run another query on my first query results so I don't mess my first query up. Here it is:

SELECT DISTINCT [Prod #], [Prod Name], [Prod Description], [Product Type]
FROM [Combined Tables]
WHERE [Product Type]<>" ";

Please Help! Thank you in advance.


5 minutes away from pulling my last one!


View Replies !   View Related
Help Needed Convert A Blank In Char To Blank In Float
I receive blanks for a column called value and i need to represent it as a blank or NA into a colum whose datatype is float in the datawarehouse.
how is this possible because in current schenario a blank is being converted to 0
which is not the right thing.
i would like to retain the blank in conversion from char to float ,
please explain me if it can be done  or  how to overcome this issue.

View Replies !   View Related
How Do I Clean Up The SQL Server (ctp) From ADD/REMOVE Program Without The Change/remove Button
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.



View Replies !   View Related
Unable To Remove SQL Instance In Add/Remove Program
I need help,

I am having a hard time removing my SQL instance inside the Add/Remove program.  After i select the SQL Instance name and then I tried to remove it but it won't allow me to delete it.  There isn't any error message or whatsoever.  Actually, when i try to log it in my SQL Management studio, that certain sql instance name is not existing according to the message box.  Is there any way to remove the Sql Instance in my system?

I appreciate your help, Thanks

IS Support

View Replies !   View Related
SQL Server Management Studio: Can Not Remove AdventureWorksDB In &&"Add Or Remove Programs&&" Of Control Panel
Hi all,

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.

Thanks in advance,

Scott  Chang 

View Replies !   View Related
Please how can remove spaces on a date field and a text field. THanks.

View Replies !   View Related
Add SPACES In Data
i have 1 query please guide me,
here i want to add  BLANKS but not working so i have to show add spaces using  __ here any idea i can add SPACES or '  ' here.
please let me know if any. basically i want to align the data in my GRIDVIEW
Thanks in advance

View Replies !   View Related
Spaces In A Sql Server
i collected the users information without using any trim fucntion(i have implemented now)
but the data which has been already posted into my server has text with some white spaces at the beginning of data
now how to remove this white spaces in this column in online server data.
the data is something like this
so how to remove white spaces in the above column

View Replies !   View Related
Erasing Spaces!

I am tring to join two tables. There is one problem of course. There is one column I would be able to join the two tables by. This column would be Loc_Code. The only problem is that both columns are not exactly the same. They look like this:

Table 1 Table 2
Loc_Code Loc_Code
A 12345 A12345
A 12346 A12346
A 12347 A12347
A 12348 A12348

I need to erase the spaces that exists in the Loc_Code column in table 1 so that I can join with table 2.

All help would be appreciated.

View Replies !   View Related
Spaces On An Insert

I am building my insert statements dynamically and am finding that there are spaces after certain integer fields.

example Insert Table A (col1, col2, col3)
values (1 ,'2',3 )

If col1 and col3 are integer fields, is there any affect to either how the value is stored or how it will be retrieved when the table is used in a join. By inspecting the values in the table, it seems fine. Do I need to worry?



View Replies !   View Related
Trailing Spaces
If I run SELECT Len(' ') it returns 0, if SELECT Len('a ') it returns 1
I need this to return the correct length including the space that on the end. I thought it was an ansi_padding problem but even turning padding on results in a 0 length. Any ideas? Thanks!


View Replies !   View Related
Pad Characters With Spaces

I have a table with a column called Type varchar(10). I need to export the Type column to a text file at a fixed length. The length must be 10 characters. The Type column data is a variable size; that is; it can contain 3 or more characters. Is there a way I can update the Type column with spaces when the Type column < 10?

Any Suggestions?

View Replies !   View Related
White Spaces
Creating a text file using DTS, is there a function/way to take out white
spaces from columns. Example:
'1234 ','567 ' would come in text as

Thanks in advance.

View Replies !   View Related
Trimming Spaces
I need to trim what looks like two spaces from a field.


" 601274" needs to be "601274". Does anyone know the syntax?+

View Replies !   View Related
Getting Spaces In A Column
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 '

View Replies !   View Related
How To Ignore Spaces?
I'm writing a store procedure to accept search strings from user on my site. Currently, this is what I have.

Code Snippet
@schoolID int = NULL,
@scholarship varchar(250) = NULL,
@major varchar(250) = NULL,
@requirement varchar(250) = NULL
--@debug bit = 0
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SELECT * FROM [scholarship]
WHERE ([sectionID] = @schoolID OR @schoolID IS NULL)
AND ([schlrPrefix] LIKE '%' + @scholarship + '%' OR [schlrName] LIKE '%' + @scholarship + '%' OR [schlrSufix] LIKE '%' + @scholarship + '%' OR @scholarship IS NULL )
AND ([Specification] LIKE '%' + @major + '%' OR @major IS NULL )
AND ([reqr1] LIKE '%' + @requirement + '%' OR [reqr2] LIKE '%' + @requirement + '%' OR [reqr3] LIKE '%' + @requirement + '%' OR [reqr4] LIKE '%' + @requirement + '%' OR [reqr5] LIKE '%' + @requirement + '%' OR @requirement IS NULL )

The problem is, somtimes the search doesn't work if there is a space behind or infront of the search string. I wonder if there is away to ignore any spaces and go right into whatever character comes next or after. If so, how do I implement that?

View Replies !   View Related
Trailing Spaces - Such A Pain...
Hi All...  I'm using a SQL Server 2005 database.  I've noticed that columns that are declared as "char" and that have a fixed size tend to put trailing spaces at the end of the data when I pull it out.  I guess I can understand why...  But it's a pain dealing with it.   As I'm bringing my application up, I can see spaces all over the place - I just havent gotten around to doing anything about it yet.  What's the easiest/best way to get rid of those spaces.  Geez, it'd be real cool if I could put something in the SELECT statement.  Any thoughts?  Thanks much!!  -- Curt

View Replies !   View Related
Dbuse Function, And Spaces
Hi,We have a problem when using the dbuse function, with a database thatinclude a space in its name.Any suggestions ???I try to wrape it with 'db name' - got 000170 015 Line 1: Incorrectsyntax nearAnd with "db name" and got the same.ThanksEyal

View Replies !   View Related
Convert Punctuation To Spaces?
Hi,I have a table of text. I need to search for whole words within this text...For example, I need to be able to search for records that contain 'dog' butnot return 'hotdog' or 'dogma' for example.I am doing this by throwing a space around both the records in the table andthe search word like this:WHERE (' ' + Text + ' ') Like ('% ' + Search + ' %')The problem is that punctuation needs to be stripped out of the text so thatit will still find "...walking the dog."Is there a way to update, converting a certain set of characters intoanother character (i.e. a space) and/or to do the same thing during the wordsearch query itself?Thanks!

View Replies !   View Related

Copyright © 2005-08, All rights reserved