Spaces In Column Names
Apr 6, 2006Hi all,
Is it a bad practice to create column names with spaces. like [Last Modified On]?
If yes, y?
ADVERTISEMENT
Spaces In SQL 7 Database Names
Mar 7, 2000Hey 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
Truncate Tables With Spaces In Their Names Using STP
Dec 16, 2005I am attempting to how to truncate list of tables using STP. That is decalre a cursor for a list table names and then to truncate the table names one by one.
The code below shows what I want to achieve. I want to truncate all the tables with names beginning with ZZ but this is failing. I have tried using both delete and truncate.
Is it possible and what do I need to do?
-- Code
SET QUOTED_IDENTIFIER Off
GO
SET ANSI_NULLS ON
GO
create PROCEDURE dbo.Empty_ZZ
AS
DECLARE @tablename sysname
DECLARE @localname varchar(50)
DECLARE ZZtablenames_cursor CURSOR FOR
select [name]
from sysobjects
WHERE [xtype] = 'U'
and name like 'ZZ %'
OPEN ZZtablenames_cursor
FETCH NEXT FROM ZZtablenames_cursor
into @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
set @localname = '[' + @tablename + ']'
TRUNCATE + @localname
FETCH NEXT FROM ZZtablenames_cursor
END
CLOSE ZZtablenames_cursor
DEALLOCATE ZZtablenames_cursor
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Create Login Names Without Spaces
Dec 31, 2004Afternoon,
I've got this security database that needs to create a login name using first initial and 7 characters of the lastname. No problem.
I wrote this:
select
CASE WHEN LastName like '%.%' then
LEFT(FirstName, 1)+left(replace(LastName,'.',''),7)
else
LEFT(FirstName, 1)+ LEFT(LastName,7)
end as UserID
from Security.dbo.tblUserInformation
The replace and case functions I used to get rid of the '.' Like the name St. Clair now I get "GSt Clai" I need it to also get rid of the space but am stuck. Any thoughts would be appreciated.
Thanks
Laura
Dtutil And Spaces In Package Names
Nov 1, 2007I'm having trouble running a statement like this:
dtutil /DTS MSDB/Budget/BUD_Production - dtsChart of Accounts Budget - Final /En FILE;BUD_Production - dtsChart of Accounts Budget - Final.dtsx;4
I've tried:
dtutil /DTS "MSDB/Budget/BUD_Production - dtsChart of Accounts Budget - Final" /En FILE;"BUD_Production - dtsChart of Accounts Budget - Final.dtsx";4
dtutil /DTS "MSDB/Budget/BUD_Production - dtsChart of Accounts Budget - Final" /En "FILE;BUD_Production - dtsChart of Accounts Budget - Final.dtsx;4"
Any suggestions? I'm no DOS pro, but I can normally get things going.
Thanks
Sam
T-SQL (SS2K8) :: Embedded Spaces Ever Valid In Non-delimited Names?
Aug 29, 2014I am trying to debug a procedure which is timing out, not in every case, but in some cases.
During my perusals, I note that there seems to be a stray space in the 3rd line of following fragment of an UPDATE statement in the procedure:
...
FROM dbo.LoadMedicationsGP LMGP
INNER JOIN dbo.Patient P ON LMGP.PatientId = P.PatientId
INNER JOIN dbo. PatientMonths PM ON P.PatientKey = PM.PatientKey
AND LMGP.DatePeriodKey = PM.DatePeriodKey
...
SSMS seems to parse the statement perfectly OK, but to me it looks as if it ought to be wrong.
I suspect that the space has no material effect, but I just wanted some confirmation one way or the other.
Determine Table Names And Column Names At Runtime?
Jan 22, 2004Hi
I was wondering if anyone has an idea of how we could find the table names and column names of the tables in our Sql server database at runtime/dynamically given our connection string? Please let me know.
Thanks.
Transact SQL :: Add Spaces To First Column So That Second Column Will Be Aligned
Sep 14, 2015I want to add spaces (like space - len(col)) to first column so that second column will be aligned when exported to email (text).
DECLARE @ColumnSpaces TABLE (
Col_1 VARCHAR(50),
Col_2 VARCHAR(50)
)
INSERT INTO @ColumnSpaces VALUES ('AAA', '123')
INSERT INTO @ColumnSpaces VALUES ('AAAAAAAAAAAAAAA', '123')
Getting Spaces In A Column
Mar 7, 2008I 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.
All Spaces In A CHAR(5) Column
Jan 31, 2005i'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?
Inserting A Value With Spaces Into A Column
Jun 17, 2008Hello,
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
How To Get Values From Column With Trailing Spaces
Jul 23, 2013Performing 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?
Need Your Help To Remove Spaces In The Column Entries Using SQL
Oct 21, 2006Hi all,I am new to these so plz never mind if this is funny.here 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 RelatedRemoving Spaces From An Nvarchar Column
Jul 20, 2005Hello,This is a simple question, hopefully with a simple answer. I havean nvarchar column of length 255. In one of the rows I have thefollowing sentance - 'See the brown ball bounce'. Is it possible touse a command to remove all of the spaces in that sentance, so thatthe sentance reads 'Seethebrownballbounce'? As you can see, I am notjust interested in getting rid of the trailing and leading spaces.Thanks,Billy
View 1 Replies View RelatedFill Up Spaces With Dots In A Column
Sep 28, 2006Hi,
I currently have a column in a table with data type char and length 500. However, not every column fills up the entire 500 length, and I would like to fill up the rest of the spaces with dots. Is there a setting in SQL to do this? I do not want to use varchar since I want a fixed length with dots at the end. Any ideas?
Thanks,
Alan
How To Set 0 Instead Of Blank Spaces To A Column Or Field.
Aug 20, 2007Hi 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 RelatedRemoving White Spaces In A Varchar Column
Sep 15, 2005I have a table . It has a nullable column called AccountNumber, whichis of varchar type. The AccountNumber is alpha-numeric. I want to takedata from this table and process it for my application. Before doingthat I would like to filter out duplicate AccountNumbers. I get most ofthe duplicates filtered out by using this query:select * from customerswhere AccountNumber NOT IN (select AccountNumber from customers whereAccountNumber <> '' group by AccountNumber having count(AccountNumber)[color=blue]> 1)[/color]But there are few duplicate entries where the actual AccountNumber issame, but there is a trailing space in first one, and hence thisduplicate records are not getting filtered out. e.g"abc123<white-space>" and "abc123" are considered two different entriesby above query.I ran a query like :update customers set AccountNumber = LTRIM(RTRIM(AccountNumber)But even after this query, the trailing space remains, and I am notable to filter out those entries.Am I missing anything here? Can somebody help me in making sure Ifilter out all duplicate entries ?Thanks,Rad
View 3 Replies View RelatedTransact SQL :: Remove Trailing Spaces From A Column
Aug 31, 2015The table I have is:
CREATE TABLE [dbo].[FTE2015](
[Firm Number] [varchar](50) NULL,
[w9] [varchar](50) NULL
) ON [PRIMARY]
GO
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)
709485""
040898A"
709502""
041382""
709503""
How can I resolve this? The [Firm Number]column is not of a fixed length.
DTS- Destination Column Name Has Spaces Causing Error
Jan 13, 2007I am using Advantage ODBC 6.2 to connect to a Advantage Data Server to
push data from a SQL table into this server. I can view the data from
the ADS with DTS and I can insert data by using "Insert into TempTable ("Last
Name","First Name") values ('test','test)" from a Execute SQL Statement. The issue is when I build
up a DTS package to pull from SQL into the Advantage ODBC, I get a
"missing closing ")"" error. I have narrowed it down to the column
names in the destination table having spaces (Last Name, First Name,
and a bunch of others. DBA 101 here). How do I beat this?
I have tried editing the destination column names in disconnected edit
by adding the double quotes but get a "Column Name '"Last Name"' not
found" error. Wrapping them with [] doesn't work. I think this is a
limit on Advantage.
When I try to use Advantage OLE DB, I get an 'Ace32.dll must be newer
then the other dll" error. I am afraid of upgrading Ace32.dll and not
break other things.
So with this said my two questions:
How do I get the destniation column names wrapped in double quotes when using transformations?
or
Is there a way I can do an Insert into Advantage connection(column names) values (Select Values from SQL Server connection)?
Table Column Names = Dataset Column Values?!
Apr 28, 2008
I need to create the following table in reporting services
PRODUCT April March Feb
2008 2007 2008 2007 2008 2007
chair 8 9 7 4 4 4
table 3 4 5 6 4 6
My problem is the month names are a column in the dataset, but I dont know how to get it to fill as column headers???
Thanks in advance!!!
Integration Services :: SSIS - Data Load To Excel - How To Retain Trailing Spaces In Text Column
May 8, 2015I 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 RelatedGetting The Column Names
Dec 29, 2003Hello,
Im trying to get the column names from a database and display them in textboxes. someone has already helped me by tellnig me that i need to use the FillSchema command. Which works just fine and I can see only the colum names in a datagrid when i bind it to that.
The problem is that I do not know how to extract the name of a column and put it in to a textbox ?
does anybody know how I can fo this ?
Thanks a million
Rob
Get Column Names
Aug 24, 2005Hi,How do I display the column names from my Sql server table?In asp3 the recordset allowed thisforeach key in rs ColumnName = key.name ColumnValue = key.valuenextHow do I do this in .Net?I want to use a DataReader so I can read through each record and only display the ones I want.TABLE_ONEColumn_OneColumn_TwoColumn_Threethanks,
View 1 Replies View RelatedI Need Column Names !!
Mar 30, 2004Hi, i need a query to have the columns names !!
If you have another solution, let me know !!
superj !!!
Column Names
Apr 10, 2008Hi
When I run this query..
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='TableTest'
I get all the column names, but the result is not in the same order as when I check the design for the table, why is that?
Column Names
Dec 27, 2005At my work, we are upgrading a number of MS Access 2000 databases to SQL Server 2000. Many of the columns have names that do not follow the rules for Identifiers. For example there are columns with a numeric names 1,2,3,4,5,6,7,8,9. There are also columns with a hyphen in the name and columns that begin with a number and not an underscore or an alpha character. Plus there are columns with names like first, last, position, etc. (There was also columns with reserved words used as the names. I have changed those column names in the databases that I have already converted) Will leaving these names alone create a problem? The database serves as the backend to ASP pages.
Miranda
Dynamic Column Names In A SP
Jan 1, 2008Hello,I'm still extremely novice to SQL and I've tried googling how to produce this result but I've been encountering a lot dynamic sql commands which isn't exactly what I want. If this is an ignorant question I do apologize but heres my scenario: I have a table with columns of the day, for instance: Monday_hasData (bit)Monday_DataAnd what I want to do is essentially pass in "Monday" as a parameter and rather than doing an If statement on each row, I would like to just like to do something like assign @dayCheck + "_hasData" to a variable and then use @dayCheck as part of my query. Is this possible or am I going to have to have 7 cases? Thank you for any input,Chance
View 2 Replies View RelatedKnow Ing The Table Names When I Know Column Name
May 30, 2008Hi all,
By using below query i can get no of tables having the give column name in a particular database,
SELECT COUNT(*) AS CounterFROM syscolumnsWHERE (name = 'empno')
but i want to know the table names too?
any one please suggest me how to find table names too......
In other words i know the particular column name and right now i want to know the table names in which this column name exists.
How To Get Column Names Which Are Not Null
Jul 12, 2002Hi,
I have a table in which out of 20 columns, there will be data only in a few columns. So, I need to find those column names which have data in them. I was able to do it using the information_schema and using a cursor to loop through all the column names and find out which columns have data in them. But i need to know if there is a more efficient way of doing this without using the cursor. Can somebody please let me know how this can be done without using a cursor? Thank you.
SQL To List Column Names
Nov 26, 2002Can anyone help me with a SQL statement that will list all the column names in a table please ?
I just want to list out the column name so that i can develop asp/vb more effectively than having to use SQLEM and Design table to see the field names.:confused:
thanks
FatherJack
Dynamic Column Names
Jun 10, 2004Hi is possible to create dynamic column name
example
Declare
@StartDate as dateTime
Select @StartDate = '2004-06-05'
select
SUM(Case When table1_date BETWEEN dateadd(day,-6,@StartDate) and @StartDate then 1 else 0 end)AS [dateadd(day,-6,@StartDate)],
SUM(Case When table1_date BETWEEN dateadd(day,-13,@StartDate) and dateadd(day,-7,@StartDate) then 1 else 0 end)AS [dateadd(day,-13,@StartDate)]
from
table1
Column Names As Parameters
Aug 19, 2004I would like to pass into a stored proc the column names I want it to return; how do I do this please?
So I pass in say:
@p_AccountNumber with a value of 'AccountNum'
and the SELECT that the sp fires is of the form
SELECT AccountNum from AccountTable
Any help appreciated
Dynamic Column Names
Jan 19, 2005thanx for the help in advance,
i have a table with Monday, Tuesday, Wednesday. .... Sunday
i get the weekday by
select @stat = datename(dw, getdate()) which in this case is Wednesday
but when i do something like
select top 2 * from myTable
where 'myTable.'+@stat = 1
which is as same as
select top 2 * from myTable
where myTable.wednesday = 1
i receive a syntax error. how can i dynamically select myTable.Wednesday ?
i also tried using
select top 2 * from Intercon
where '@stat' = '1'
but then this doesn't return anything where it should.
any help?