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 Replies


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

Copyrights 2005-15, All rights reserved