Help on TSQL Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Help on TSQL Query

Author

Topic

cnubabudwh
Starting Member

1 Post

Posted - 2013-03-20 : 04:55:31

In the query below TextColumn contains string data, NullColumn is always null and TimeColumn contains value of data type time(0). What is the output.
Hint: CONCAT is a new SQL2012 function.
SELECT * FROM Table1
WHERE CONCAT(TextColumn, NullColumn, TimeColumn) > ''

A data conversion error will occur.
A recordset of all columns and all records will be returned.
An empty recordset will be returned.
A syntax error will occur.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-20 : 05:38:26

I think this is the answer........
A recordset of all columns and all records will be returned.

Support Documents are:
http://raresql.com/tag/sql-server-2012-concat/
http://www.sql-server-helper.com/sql-server-2012/sql-server-2012-new-string-functions.aspx

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-20 : 11:55:54

why not test it with an illustration


declare @table table
(
Textcol text,
NullCol varchar(50),
timecol time
)

insert @table
values('wefdwef3f3f3ffwefw	f',NULL,'18:02')
SELECT * FROM @table
WHERE CONCAT(TextCol, NullCol, TimeCol) > ''


output
-----------------------------------------------
Textcol	                       NullCol	timecol
------------------------------------------------
wefdwef3f3f3ffwefw	f	NULL	18:02:00.0000000

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-21 : 00:03:01

Hi visakh,

I don't have MSSQL 2012 version... I have provided answer as per documents

UnemployedInOz
Yak Posting Veteran

54 Posts

Posted - 2013-03-21 : 01:55:13

Create table #Table1
(TextColumn varchar(10),
NullColumn varchar(10),
TimeColumn time)

Insert into #Table1 select 'ABC',null,CURRENT_TIMESTAMP

SELECT CONCAT(TextColumn, NullColumn, TimeColumn) FROM #Table1

-- Result
ABC16:53:28.1430000

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-21 : 02:07:22

quote:
Originally posted by UnemployedInOz

Create table #Table1
(TextColumn varchar(10),
NullColumn varchar(10),
TimeColumn time)

Insert into #Table1 select 'ABC',null,CURRENT_TIMESTAMP

SELECT CONCAT(TextColumn, NullColumn, TimeColumn) FROM #Table1

-- Result
ABC16:53:28.1430000

We have already provided answer....
In your script, put TIME(0) instead of type TIME... (OP asked for timeColumn as TIME(0) type)..
Any way the answer is A recordset of all columns and all records will be returned.

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-21 : 02:18:46

quote:
Originally posted by bandi

Hi visakh,

I don't have MSSQL 2012 version... I have provided answer as per documents

That was not for you but for OP

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources