Hi, there;
We know that: Both select * from mytable where column1="data" and select * from mytable where column1="data " give us same result. (Please note the spaces in second query) This means the trailing space doesn't affect the query result.
How can I make SQL to return different result?
Thanks.
I have a databound textbox that is used to store a decimal value.
If my sql table stores this column as a decimal(2,2), then all of the numbers entered into the field will automatically put decimal places in that I don't want. For example, 45 becomes 45.00... 34.5 becomes 34.50.
If I set the sql table to nchar(10) and the dataset to system.string (max length of -1), then the number looks the way I would like it, however after a datatable update I end up with trailing whitespace after the number - filling up the rest of the unused 10 characters. For example, "45" becomes "45 " (8 spaces afterwards).
Does anybody know how I can fix this? I would prefer to store the numbers in SQL as a string (nchar(10))... but I don't know how to get rid of that darned whitespace. I would like to remove it at the database level and not at the client level if at all possible.
Thanks!
Ok following scenario
create table dbo.ADAssets
(Computername nvarchar(max))
insert into dbo.ADAssets
values
('AIRLBEOF3565 CNF:4e926e06-6f62-4864-aebd-6311543d',
'AIRLBEOF3565')
So if execute the following
select Computername
from dbo.ADAssets
where Computername like
'AIRLBEOF3565%'
I get both records,but if I do this
select *
from dbo.ADAssets
where Computername in
(
'AIRLBEOF3565 CNF:4e926e06-6f62-4864-aebd-6311543d',
'AIRLBEOF3565'
)
I only get AIRLBEOF3565
So the big picture is that I need to compare 2 tables to find records that match & don't but that I get matches that shouldn't be & matches that aren't.
Hi,
I am trying to do this:
UPDATE Users SET uniqueurl = replaceAllEmptySpacesInUniqueURL('uniqueurl')
What would be the syntax.
Any help appreciated.
Thanks
I am generating a Report from Sql Data Source in Sql Server 2005 and viewing the Report in Report viewer control of Visual Studio 2005.
The data in the Data Source contains string with multiple spaces (for example €œ Test String €œ) but when they get rendered in Report viewer control, multiple spaces gets converted to single space €? Test String €œ.
I tried following solutions
1) Replacing spaces with €œ €?
2) Inserting <pre> tag before the string and </pre> tag after the string (Also tried <Pre> instead of <pre>)
But in all the cases result is same. The Report Viewer control is showing €œ €? instead of space and €œ<Pre>€? tag instead of preserving spaces.
Please provide me a solution so that spaces can be preserved in Report Viewer.
I am using the below script to get space alerts  and now i am interested in sending alerts  if for any drive space available is Less than 10% or 15%.. how to convert beelow code to find in %Â
Declare @Drives Varchar(20)
DECLARE @Spaces Varchar(50)
DECLARE @availableSpace FLOAT
DECLARE @alertMessage Varchar(4000)
DECLARE @RecipientsList  VARCHAR(4000);
CREATE TABLE #tbldiskSpace
[Code] ....
I have a column defined as DECIMAL(10,6). I want to display it as astring but I do not want the trailing zeros. I cannot seem to get CASTor CONVERT or STR to exclude the zeros.Examples:45.340000 --> 45.3427.700000 --> 27.755.000000 --> 55Is there a function that will do this or do I need to write my own?
View 5 Replies View RelatedHi.. I was doing a good maintenance on my DB and my trans log LDF keep growing until 30GB but my DB data file MDF is only 2GB. I found the two following method to reduce my log size.
Method 1: I used veritas to backup log file with truncate
Method 2: I used the shrink database option in Enterprises manager to shrink it (file chosen=log , use default option)
After doing that, I found my LDF log file is still about the same size=27GB but when I see clearly, from the shrink database windows, the log spaced used reduced to only 100MB, the allocation log space is still 27GB. Why? How to make the LDF smaller to be the around the same size as the space used 100MB?
This is driving me bananas. Can't find any info on this anywhere....SQL 2000 seems to replace double space with a single space when I seta varchar field to " " (2spaces), it only stores " " (1space). Whyon earth would microsoft do this? If I save 2 spaces - I WANT TO SEE2 SPACES!!!!Can anyone help? Is this a database setting? Is this due to usingvarchar?Any help appreciated.Colin Hale
View 2 Replies View RelatedWould someone mind helping me with formatting a string please??
I have a column DECIMAL(4,2). In a stored proc I am selecting this field and converting it to varchar so I can append certain characters to it (this is really irrelevant to my question). However, before appending the characters, I need to remove zeros after the decimal point.
Examples:
3.00 should be 3
3.20 should be 3.2
3.05 should be 3.05
etc
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
Hi All,
Can anybody tell me how to remove the trailing zeros from the numeric field?
The data looks like this:
1.0000000
24.0000000
2356.0000000
61.0000000
It should look like this:
1
24
2356
61
Thanks.
Hi,
I need to delete trailing slashes ('') from values in a specified column. Something like what TRIM does for spaces, I want to do it for trailing slashes. I have column values such as Rajat, Rajneesh, Ankush, Sudheer ... etc. As a result, I need to have the column values as Rajat, Rajneesh, Ankush, Sudheer ...
Hope the question is clear. Please help me at the earliest. Thanks a lot in advance.
Hello,
Somebody know how to reduce the space allocated for the transaction log space for my SQL_DB ?
3700 MB allocated but only 100 MB used and 3600 MB are free !
Transaction log properties :
Automatically grow file are filled
file growth by percent = 5%
maximum file size - restrict filegrowth = 3700 MB (we can't reduce it !)
Thank you for your precious help !
Khaix from Brussel.
How do we suppress multiple spaces to a single space in T-SQL
E.G.
Field: FullName
e.g.
WOMENS HEALTH RIVER VALLEY
JOHN FAMILY MED GROUP
HERSH STWEART P.
PARK HEIGHTS MEDICAL CENTER
KOPP WHITEFIELD E
The o/p wanted is
HERSH STWEART P.
Thank you.
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
In my select query for field ordernumber want to add two trailing zeros in the resultset, how can i add.
Thanks for the info.
How can i add two trailing decimals if there is no decimals:
for example if it is just 1, then make it 1.00
if it is 1.12 then leave it as it is.
can you please help.
select cast(ordernumber as varchar(10)) + '00' from ordertable
Thanks for the info.
I have a OLE DB Source going to a flat file destination. My source is a sql variable with "select * from tablename" which have varchar datatypes. Yet I'm getting trailing spaces at the end of some of my columns (for instance, my address column).
I've checked the data by doing a "select Max(Len(address)) from tablename" and the max is only like 34 chars, yet each of them have 100 chars total.
Taking a look at my flat file connection, the outputColumnWidth is 100, and datatype is string [DT_STR]. Am I crazy? What's the problem here? Is the DT_STR datatype the equivalent of char, and not varchar?
Any help, of course, will be appreciated.
Simple scenario. I have a DB table with a money column. SSIS package with a data flow task which has an OLE DB source and flat file destination. I want the single money column from the database into the flat file.
If a (monetary) value in the table is 123.50, the I want 123.50 to appear in the file, not 123.5. Any ideas how I do this? I've tried all available data types, set the scale to 2 where available but nothing has an effect.
Any thoughts?
Greg.
For some reason, there are extra trailing spaces being added to all my
data as it is placed in the db. I am collecting information from,
processing a TRIM-like function in javascript, and then again in the
ASP.net code before it is being placed in the db.
The extra spaces are causing problems with my application.
Any help would be greatly appreciated.
I have a column that is varchar(12) that the data was entered left justified
such as '12345 ' with trailing spaces. I need to move the number to be right justified to link with another table so it looks like ' 12345'. I looked at the right command and could not find a solution. An ideas?
Thanks
Performing security audit using command to retrieve data from Active Director about security groups and drop results into local tbl for analysis.
EXEC xp_cmdshell 'net group "AnalyticsDev" /domain'
Problem is the col created to store result is varchar(1000) and can hold 1-3 values (loginIDs) per row with lots of trailing/white space.
E.g. (EmpID101, EmpID250 EmpID10)
Is there a technique to extract the needed value (loginIDs) from col?
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 RelatedHiI use SqlServer 2000I am doing a select and sending the results, which is a cast() intodecimal (9,3), in an email to various other users of our system.Problem is that a number like 95.2 is display as 95.200. Is there anyway I can trim it so that it will display 95.2 ?David
View 1 Replies View RelatedI am trying to export data from a SQLServer database into a text fileusing a stored procedure. I want to be able to read it and debug iteasily; therefore, I want all the columns to indent nicely. This meansI need to append trailing spaces to a text string (such as "Test1 ")or append leading space in front of a text string that contains anumber (such as " 12.00"). Now, the stored procedure works fine whenI run it in Query Analyzer. But it doesn't work correctly when I runit using ISQL - All the columns are not indented. I am wondering whyit doesn't work in ISQL.This is what I want, and this is also what I get when I run the storedprocedure using Query Analyzer:Test1 , 2,Test1.txt , 1.00, 1.00Test22 , 2,Test22.txt , ,Test333 , 2,Test333.txt , 30.00, 30.00This is what I get if I run the stored procedure using ISQL(isql -S myserver -E -w 556 -h-1 -n -d mydb -Q "exec MyTest"):Test1, 2,Test1.txt, 1.00, 1.00Test22, 2,Test22.txt, ,Test333, 2,Test333.txt, 30.00, 30.00You can see that the result from ISQL has the following differences:1. It puts a space in front of each row.2. It appends enough spaces at the end of each line to makethe line length to be exactly 61 characters.3. It gets rid of the trailing space from each column.4. It leaves only one blank space if the column has nothingbut a serie of spaces.The following is the stored procedure that I am testing:create procedure MyTestasset nocount oncreate table #Test(Field1 varchar(10) null,Field2 varchar( 5) null,Field3 varchar(20) null,Field4 varchar(10) null,Field5 varchar(10) null)insert into #Test values( "Test1 ", " 2","Test1.txt ", " 1.00", " 1.00" )insert into #Test values( "Test22 ", " 2","Test22.txt ", " ", " " )insert into #Test values( "Test333 ", " 2","Test333.txt ", " 30.00", " 30.00" )select Field1 + "," +Field2 + "," +Field3 + "," +Field4 + "," +Field5from #Testdrop table #TestgoStrangely, the differences #3 and #4 only show up when I use theSELECT statement on a table. They don't show up when I use SELECTstatements to show constant text strings or string variables, likethis:set nocount onselect "Test1 " + "," +" 2" + "," +"Test1.txt " + "," +" 1.00" + "," +" 1.00"select "Test22 " + "," +" 2" + "," +"Test22.txt " + "," +" " + "," +" "select "Test333 " + "," +" 2" + "," +"Test333.txt " + "," +" 30.00" + "," +" 30.00"The result is like the following if I use constant text strings orstring variables:Test1 , 2,Test1.txt , 1.00, 1.00Test22 , 2,Test22.txt , ,Test333 , 2,Test333.txt , 30.00, 30.00I need to run it from ISQL because that is how I run _all_ my otherstored procedures. I don't want to do anything differently justbecause I need to run this stored procedure.Thanks in advance for any suggestion.Jay Chan
View 4 Replies View RelatedI 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.
View 1 Replies View Related
Hi
Is there any way to trim all the leading and trailing spaces in all the column in a table.
JigJan
I have a column of type Decimal(14, 4) in my SQL server 2005 database.
When producing the result set on this column, i need to convert the values to varchar datatype and also i do not want the trailing zeroes to be displayed.
For eg:
If the value stored is 98.7500, my select query should provide an output of 98.75.
Similarly if the value stored is 98.0000, my select query should output 98.
I tried converting the value to "float" and then assigning to varchar data, however doing so i am incurring precision loss in some scenarios
See below:
Declare @ele as varchar(25)
Select @ele = Convert(float, 99999.9990)
select @ele
Output I received: 100000
Output I wanted: 99999.999
Are there any in-built functions in SQL to achieve what I need?
Hi All,
I have a column which has some white spaces that I suspect is tab delimeted one. So when I use a rtrim(col1) it would not trim those. So i used a scrip component and wrote this line,
Row.trimDetail = RTrim(Row.detail)
here trimdetail is an o/p column and detail is the input col with the trailing spaces.
but still I don know why the column has that spaces. Can someone help me to figure out what is the problem ?
Thanks in advance,
Hi All,
For €œnvarchar€? column if we pass the value as spaces, then it stores as space.
Eg:
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,
Palani
I am trying to pad a fixed number of trailing spaces into a report expression, as follows:
Data:
CUSTOMER_NAME-------------MichaelPeterJohn
Result (e.g. with 10 spaces padded, assuming all names are below 10 characters long):
"Michael ""Peter ""John "
Is there an easy way to achieve this ?
Thanks.
Kenny
I'm uploading data from a CSV file into SQL Server 2000 using an upload routine in C# (ASP.NET 2.0). I'm using an OleDbDataAdapter to select all the data in the CSV file into a DataTable. I then use a SQLBulkCopy to copy all the data into my SQL Server table. The CSV file maps exactly to the fields in the SQL Server database and I've used a custom delimiter of a '~' (tilda) that is declared in a schema.ini file.
The import works fine and my SQL Server table is fully populated with all the data. However, one source field in the CSV file is getting truncated in my SQL Server table. It is a field containing a value e.g. 32,000, 64,500 but this is getting truncated and losing the trailing zeros in SQL Server i.e. 32,000 (CSV) becomes 32 (SQL Server) and 64,500 becomes 65,5.
I've tried mapping the source field to a varchar, nvarchar and text field, but all data types have the same problem with truncating the trailing zeros (I only need to display the data - no calculations required) and the ',' in the number in the CSV file has prevented me from mapping this to an integer.
Also, locally the code works fine and my local database is SQL Server 2005, but the remote host is using SQL Server 2000 and this is where the problem occurs.
Any help would be appreciated! Thanks