Hi
i have 1 query please guide me,
SELECT P.ProjectName, REPLACE(SPACE(TU.TaskOutlineLevel), ' ', '__') AS dash,TU.TASKISSUMMARY AS TASKSUMMARY,
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
Parth
Hi,
Whenever I insert a record into my table it adds trailing white spaces up to the amount of char's that the field is set to allow. Obviously I don't want it to do this.
Among other problems then when I get the data back out it has a ton of white spaces, which normally wouldn't be a problem. i could just use the .Trim() function, but for some reason when I bind the data to a drop down list and use the .Trim() function it doesn't trim the white spaces.
Anyways any ideas on how to make it so the white spaces don't get put in in the first place?? Or any other thoughts on this?? Thanks!
Hello,when I export data from a table to a text file, I get trailing spacesif the data type in char. (This dosen't happen if the data type isvarchar). I can get rid of the spaces by using the trim() function onevery signle column. here is an example:DTSDestination("first_name") = DTSSource("last_name")My question is:Is there any easier way to get ride of the training spaces for allcolumns when exporing a table? It is too time consuming if I have totype trim() for every single column in the table.Thank you in advance,Eddy
View 1 Replies View RelatedHi ,
My Input is a flat file source and it has spaces in few columns in the data . These columns are linked to another table as a foreign key and when i try loading them in a relational structure Foreigh key violation is occuring , is there a standard method to replace these spaces .
what approach should i take so that data gets loaded in a relational structure.
for example
Name Age Salary Address
dsds 23 fghghgh
Salary description level
2345 nnncncn 4
here salary is used in this example , the datatype is char in real scenario
what approach should i take to load the data in with cleansing the spaces in ssis
In SQL Reporting I want the user to pass a parameter to the report; the parameter is a employee number. Easy right, you write a where statement like where table_name.employee_number =@employee_number. When the user runs the report they€™re asked to type in the number.
My problem is the dB stores the employee number with leading spaces padding the number out to 9, so if the user types in 1 they get jack, but if they type in €˜ 1€™ they get the right employee. I can€™t roll it out that way
I€™d do something in the where clause so the user could just type in 1 & it would auto pad out to 9 spaces. I€™ve tried using the €˜LIKE€™ clause & I€™ve tried using concatenate but so far neither works well.
I'd appreciate any advice
Thanks -- Vince
I understand that we shouldn't shrink data files as it might cause heavy fragmentation along with log usage, high IO/CPU etc.
In a DB in which lot of DML transaction occur, there will be empty spaces whenever deletions occur.
Will SQL Server fill that part with data when new insertions occur ?.
Hello,
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.
Thanks
I am loading data using SSIS 2008 from a table in SQL Server 2008 DB to excel 97 sheet pre-defined with column headers. All the columns in excel is has 'Text' format property and the columns in the SQL Server table are defined as nVarchar. One of the columns has trailing spaces in few rows in DB but after exporting to excel 97, the spaces are gone. We need to retain the whitespaces in the column values. How can we do that.
View 3 Replies View RelatedPlease how can remove spaces on a date field and a text field. THanks.
View 1 Replies View Relatedi 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
mycolumn
data
data
data
data
so how to remove white spaces in the above column
How can you remove spaces in the middle of a string, RTRIM and LTRIM does not work
View 1 Replies View RelatedCreating 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
'1234','567'
Thanks in advance.
Hi,
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?
Thanks,
Jim
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!
Todd
--------------------------------------------------------------------------------
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.
I need to trim what looks like two spaces from a field.
Example:
" 601274" needs to be "601274". Does anyone know the syntax?+
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 RelatedI'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
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
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?
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 '
Thanks.
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
What is the best way to check for leading spaces in your table, using ltrim?
Such as TableA(name, city)
the data in TableA Smith Dallas
John New York
Greg Richmond
David Chicago
Return only David.
Hey all,
Hope I can get some help here.
Have a view that has to reference a table in another database on the same server. This isn't the problem. I am just using Databasename..tablename in the from statement. Get the results required in testing.
The problem is that in production the table name has a space character. i.e. 'accounting info' would be the table name. I have not been able to figure out the proper syntax to capture this properly to reference in the from statement.
I have tried enclosing the name in ' ', " ", [], (), {} and just about every combination I can come up with.
Any help in this would be great, and changing the databse name isn't an option at this time.
Thanx,
Chris
Hi all smart people,
Can someone please help me with how to replace null values with spaces in select statement.
I have to have a select statement which will select all the data but the field contain null value it has to replace with spaces. How can I do that ?
Thanks,
I have some fields in my database that are char(50)
I use an insert to put some text into the fields (using perl)
when I look at the fields I find that spaces are used to "pad out" the text to the field size.
This didn't happen with MySQL and PHP, is there any way to disable this?
Hi !
I'm trying to put a space after . and , in varchar fields.
I've tried
UPDATE Resultados
SET RespuestaAbierta = REPLACE(RespuestaAbierta, ',', ', ')
but nothing happened
What's wrong ??
Thanks and sorry if my english isn´t so good.
Eduardo (from Argentina)
HI all,
I have a database that allocated:
Data: 7300 MB
Log: 2000 MB
But only used
Data: 5500MB
Log: 50MB
How can I free the unused space in the transaction log because
the database is getting too big.
Thank you for your suggestion.
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?
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 know this is probably a DOS question but does anyone know how the following would work without taking out the spaces in the folder "Audit Tax Planning" (works fine in paths without spaces)?
Declare @ren varchar(255)
Declare @path varchar(100)
Set @path = '\mainWGDATAAccountingAudit Tax Planning'--G:AccountingAudit Tax Planning 5BE
IF EXISTS(SELECT id FROM tempdb..sysobjects WHERE id = object_ID('tempdb..#Tax_File'))
DROP TABLE #Tax_File
CREATE TABLE [#Tax_File](
[File_Name] varchar(50),
)
Set @ren = 'dir /B ' + @path + 'test.txt'
Insert into #Tax_File ([File_Name])
EXEC master..xp_cmdshell @ren
If not exists(select [File_Name]
from #Tax_File
where right([File_Name],4) = '.txt')
begin
RAISERROR ('File does not exist.', 16, 1)
Return
end
select * from #Tax_File
Hello,
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
postcode|carrier_code|available|depot_code|hub_code|county_code
----------------------------------------------------------------
EX15.....|NF/S93......|........1|34........|C.......|DEVO
EX15.....|NF/SAT......|........1|34........|C.......|DEVO
EX15.....|NF/930......|........1|34........|C.......|DEVO
EX15.....|NF/AM.......|........1|34........|C.......|DEVO
EX15.....|NF/ON.......|........1|34........|C.......|DEVO
EX15.....|NF/48.......|........1|34........|C.......|DEVO
EX15.....|NF/3D.......|........1|34........|C.......|DEVO
Here is a snap of how I would like to set up another entry:
postcode|carrier_code|available|depot_code|hub_code|county_code
----------------------------------------------------------------
EX15 1...|NF/S93......|........1|34........|C.......|DEVO
EX15 1...|NF/SAT......|........1|34........|C.......|DEVO
EX15 1...|NF/930......|........1|34........|C.......|DEVO
EX15 1...|NF/AM.......|........1|34........|C.......|DEVO
EX15 1...|NF/ON.......|........1|34........|C.......|DEVO
EX15 1...|NF/48.......|........1|34........|C.......|DEVO
EX15 1...|NF/3D.......|........1|34........|C.......|DEVO
Thanks
All- I'm using the SQL SP below to drive an asp.net gridview. Note how, after "else", I attempt to send to the browser the HTML code that should create a hard space. However, the characters are simply rendered verbatim by the browser. I've tried putting actual spaces in the quotes, and also the SPACE(x) argument. SP test executes appear to show the spaces in the results, but aps.net isn't rendering the spaces at all. Any thoughts.
(Its really more of an asp.net question, but I thought I'd post this here too for your thoughts. Thx in advance.)
SELECT person_id, male, female, last, CASE WHEN fam_adult = 1 then first else ' ' + first end as first, fam_adult, is_adult, is_kid, is_y_parent_or_kid, is_guest, is_person_type_5, grade, school, is_person_type_6, fam_dad
FROM person
In a table I have a coulmn of postal-codes where there is a space in the middle of the postcode. How do I do to trim them away?
View 6 Replies View Relatedi 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)
begin
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
end