How To Remove Blank Spaces In Records??

Jun 23, 2005

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 5 Replies


Reporting Services :: How To Remove Blank Spaces After Removing Duplicated Records

Aug 18, 2015

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).

View 5 Replies View Related

How To Remove The Blank Spaces Between Subreports In Sql Server Reporting Service?

Feb 14, 2007

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 2 Replies View Related

Replace Blank Spaces

Oct 3, 2006

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 13 Replies View Related

Varchar And Blank Spaces In SQL DB Field

Jun 30, 2005

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 3 Replies View Related

How To Set 0 Instead Of Blank Spaces To A Column Or Field.

Aug 20, 2007

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 5 Replies View Related

SQL 2012 :: Select Rows With Blank Spaces

Feb 12, 2014

I got two columns DEATHDATE and STATUS

I have to update STATUS Column as INVALID when data in DEATHDATE column as follows

Example of INAVLID DEATH DATE : 1221, 12xt88,12#10, 1, ABC, #@ , etc

Do nothing when the DEATHDATE is VALID like

Examples of Valid/accepatable DEATHDATE: 09122012, 091212 ,******,********, 000000,00000000

How can i achieve this.

View 1 Replies View Related

Remove Spaces

Apr 12, 2006

How can you remove spaces in the middle of a string, RTRIM and LTRIM does not work

View 1 Replies View Related

Remove Spaces

Jan 25, 2007

is there a way to do remove spaces from a string if its they are in deifferent places on each row

View 1 Replies View Related

Replace Nulls With Blank Spaces In Float Data Type

May 8, 2008

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 7 Replies View Related

Remove Leading Zeroes And Keep Spaces

Sep 27, 2001

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 3 Replies View Related

How To Remove White Spaces Between Words

May 30, 2008

What is the Select statement to remove white spaces between words?

View 4 Replies View Related

Need Your Help To Remove Spaces In The Column Entries Using SQL

Oct 21, 2006

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 4 Replies View Related

Need To Remove Extra Spaces In Query Output

Sep 15, 2006

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 4 Replies View Related

SQL Server 2008 :: How To Remove Spaces From Output

Jun 23, 2015

When I save my output (from a query I ran) to a text file, there seems to be rows of spaces. Is there a way i can just kill off any spaces at the end of my query? Like rtrim or something?

View 9 Replies View Related

Transact SQL :: Remove Trailing Spaces From A Column

Aug 31, 2015

The table I have is:

CREATE TABLE [dbo].[FTE2015](
[Firm Number] [varchar](50) NULL,
[w9] [varchar](50) NULL

select * from dbo.FTE2015
Firm Number w9
709485""   0
040898A" 12.5
709502"" 2.4
041382"" 0.4
709503"" 0.3
709681""  4.9

How do I remove the trailing blanks? I tried RTRIM but it does not work.

SELECT RTRIM([Firm Number])
FROM dbo.FTE2015;
(No column name)

How can I resolve this? The [Firm Number]column is not of a fixed length.

View 11 Replies View Related

Transact SQL :: LTRIM And RTRIM Does Not Remove Spaces

Oct 25, 2012

I'm not sure about why I'm not able to remove spaces even after trimming them. Below is the result of query I'm usning.

select distinct LTRIM(RTRIM(Promotion_Code)) Promotion_Code
--, count(Promotion_code)
from dbo.Marketing_Promotion_Tb
where Promotion_code  like '%1BTPIZZA%'

Result :

Promotion_Code   Length
1BTPIZZA                  10
1BTPIZZA                   8

View 12 Replies View Related

Remove Alpha Chars &&amp; All Spaces In Field [UPDATED]

Apr 5, 2007


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 4 Replies View Related

Create TRIGGER Remove White Spaces From A Fields In Table-scan And Fix

Apr 21, 2008

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 21 Replies View Related

Transact SQL :: Remove Blank Rows From Table?

Apr 17, 2012

I have a table for example like following

DECLARE @tmpTable table
name varchar(10),
address1 varchar(10),
phnno varchar(10),
mobno varchar(10)
INSERT INTO @tmpTable(name,address1,phnno,mobno)

[Code] ....

I want to remove all empty rows like row 1,2 and 3 in the above example.

I can't check all columns null values as there are many columns in my actual table.

View 6 Replies View Related

Blank Spaces In Table Name, Cannot Delete Table

Mar 10, 2000

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 1 Replies View Related

Removing Records With Spaces In Fields

Mar 26, 2008

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 1 Replies View Related

Show All Records If SQL Parameter Is Blank?

Jan 1, 2008

 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 5 Replies View Related

Find Records With A Blank Field

Mar 13, 2000

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 2 Replies View Related

Replacing Blank Records With Text

Mar 20, 2006

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 6 Replies View Related

SQL / Not Existing Records As Blank Cells

Oct 7, 2007


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 1 Replies View Related

How To Set A Parameter For Filtering Not Blank Records

Feb 7, 2007

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 1 Replies View Related

T-SQL (SS2K8) :: Displaying Sum Of Records And Show Row Even If It Is Blank Or Zero

Mar 10, 2015

I am creating a report on an webpage to show how many times a coupon has been used from a set list of items.There are two tables I am using for this query.Table 1 has the list of items or coupons. Table 2 has a column for the item id from Table 1 and a value.

Table 1
id Name
1 Coupon1
2 Coupon2
3 Coupon3


My current query will only show coupon1 and coupon2 and the number and value. It will not show coupon2 since it hasn't been used. I would still like to show coupon2 with a number of zero and a value of zero.

Current query is:
SELECT Table1.Name, COUNT(Table2.ID) AS CNum, SUM(Table2.Value) AS CValue FROM Table2 JOIN Table1 ON Table2.ID = Table1.ID GROUP BY Table1.Name

View 5 Replies View Related

How I Remove Records From The Log File

Oct 20, 2001

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 1 Replies View Related

Need To Remove All Duplicate Records.

Feb 12, 2008


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 14 Replies View Related

Remove Outlying Records...

Nov 18, 2006


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 1 Replies View Related

Remove And Archive Records Into Another Table

Mar 5, 2008


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 3 Replies View Related

SQL 2012 :: How To Remove Records From Repository / Alerts

Feb 18, 2014

We configured IDERA SQL Safe for backups and restores.we setup an email for notifications. One day we performed manual backup operation for 150 databases from SQL Safe tool. Unnoticed it backed up to C:Backup folder.

We got alerts with the report of backups on C: drive. Then we moved backup files to respective folders.But, I could not clear the records from the report, its been 25 days, still we are receiving the alerts as below.How can I clear this report or to configure or setup anything to avoid this, in future as well.

Below is the sample records from the report. I need this report to be cleared.

Subject: SQL Safe Validation Report

The following files are recorded in the SQLSafe repository, but no longer exist in their locations:

C:BackupLUXOR_DB_GroupEPF_Diff_201402050045 (1 of 1).safe
C:BackupLUXOR_DB_InsightEPF_Diff_201402050045 (1 of 1).safe
C:BackupLUXOR_DB_IMEPF_Diff_201402050045 (1 of 1).safe
C:BackupLUXOR_DB_SiteEPF_Diff_201402050045 (1 of 1).safe
C:BackupLUXOR_DB_SiEPF_Diff_201402050045 (1 of 1).safe
C:BackupLUXOR_DB_ICEPF_Diff_201402050045 (1 of 1).safe
C:BackupLUXOR_DB_vdbEPF_Diff_201402050045 (1 of 1).safe
C:BackupLUXOR_DB_M_SEPF_Diff_201402050045 (1 of 1).safe

View 3 Replies View Related

Copyrights 2005-15, All rights reserved