March 2, 2009 at 10:26 pm
Hi,
I am trying to get the list of column names that have SPACE in between.
Ex:
--===== Create the test table with
CREATE TABLE mytable
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
id int,
[abc cde] varchar(10),
[abcc d] varchar(10),
[SQL DBA] varchar(10),
[Santhosh] varchar(10),
[WithOutSpace] varchar(10),
[With Space] varchar(10),
[With 2 Space] varchar(10),
[With 3 Space here] varchar(10)
)
I need the uptput as:
abc cde
abcc d
SQL DBA
With Space
With 2 Space
With 3 Space here
I tried the below but did not got the result,
declare @col_name varchar(100)
declare col_cur cursor
for
select column_name from information_schema.columns where table_name = 'mytable'
open col_cur
fetch next from col_cur into @col_name
while @@fetch_status=0
Begin
if contains(@col_name,' ') then
print @col_name
fetch next from col_cur into @col_name
end
close col_cur
deallocate col_cur
I'm getting error at the if condition
Is that the right way to use CONTAINS or am I using it wrongly?
also I tried
select column_name from information_schema.columns
where CONTAINS(column_name,' ') and table_name like 't Master%'
This gave me an error saying:
Msg 7601, Level 16, State 2, Line 1
Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'information_schema.columns' because it is not full-text indexed.
How can I get this done?
Thanks,
Santhosh
Thanks,
Santhosh
March 2, 2009 at 10:59 pm
this may help u ...
plzzzz try this. it is working
select * from information_schema.columns where column_name like '% %' and table_name like '%give the table name>%'
March 2, 2009 at 11:18 pm
dharaneeswar_reddy (3/2/2009)
this may help u ...plzzzz try this. it is working
select * from information_schema.columns where column_name like '% %' and table_name like '%give the table name>%'
Hi, Thanks
but this won't work if there are more than one space between column names
Thanks,
Santhosh
March 2, 2009 at 11:41 pm
Use below sql statment ,
select * from information_schema.columns where charindex(' ',column_name)>0
March 2, 2009 at 11:47 pm
srikant maurya (3/2/2009)
Use below sql statment ,select * from information_schema.columns where charindex(' ',column_name)>0
Thanks....it worked
Thanks,
Santhosh
March 3, 2009 at 3:48 am
Santhosh (3/2/2009)
dharaneeswar_reddy (3/2/2009)
this may help u ...plzzzz try this. it is working
select * from information_schema.columns where column_name like '% %' and table_name like '%give the table name>%'
Hi, Thanks
but this won't work if there are more than one space between column names
No. It works fine as the test is just for at least one space in the column name.
/*------------------------
use scratch
CREATE TABLE mytable
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[abc cde] varchar(10),
[abcc d] varchar(10),
[SQL DBA] varchar(10),
[Santhosh] varchar(10),
[WithOutSpace] varchar(10),
[With Space] varchar(10),
[With 2 Space] varchar(10),
[With 3 Space here] varchar(10)
)
select column_name from information_schema.columns where column_name like '% %' and table_name like 'mytable'
drop table mytable
------------------------*/
column_name
---------------------
abc cde
abcc d
SQL DBA
With Space
With 2 Space
With 3 Space here
(6 row(s) affected)
Derek
March 3, 2009 at 4:10 am
Yes, it works..
Thanks again.
Thanks,
Santhosh
March 4, 2009 at 4:00 pm
This type of query will give you the actual number of spaces in the columns:
CREATE TABLE XX_Test
( [Col 1]INT
, [Col 2]INT
, [Col 1 X]INT
, [Col 2 X] INT
, Col30INT
)
GO
SELECT TABLE_NAME, COLUMN_NAME
, LEN(COLUMN_NAME) - LEN(REPLACE(COLUMN_NAME, ' ', '')) AS SpaceCount
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'XX_Test'
AND COLUMN_NAME LIKE '% %'
Todd Fifield
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply