Ashish Dutt (12/11/2014)

---

Thanks Eirikur for your response.

But I'm greeted with the following error on executing the query you posted.

Msg 102, Level 15, State 1, Line 13

Incorrect syntax near 'b'.

The error...

There are few ways of doing this, here is a quick window function solution for SQL Server 2012/14

😎

USE tempdb;

GO

SET NOCOUNT ON;

IF OBJECT_ID('dbo.TBL_LABEL') IS NOT NULL DROP TABLE dbo.TBL_LABEL;

CREATE TABLE dbo.TBL_LABEL

(

...

Quick thought, switch from group by to window function with output de-duplication

😎

use MICE

SELECT

*

into School_Perform_Data_2020

FROM

(

select

ROW_NUMBER() OVER

(

...

My advice is to go for...

Quick thought, looks like somewhere in the dataflow a column or set of columns are defined as unicode (65001), look out for DT_WSTR or DT_NTEXT in the column properties.

😎

Further on Grant't post, here's a code snip for retrieving the execution plan

😎

USE tempdb;

GO

SET NOCOUNT ON;

/*

The query

*/

select

*

from sys.objects

/* get the execution plan */

;WITH XMLNAMESPACES...

Sean Lange (12/11/2014)

---

This looks pretty close to mine. Just before I posted my solution I saw that the OP wanted the closest date that...

Quick and simple row_number solution

😎

USE tempdb;

GO

SET NOCOUNT ON;

IF OBJECT_ID('tempdb..#TestTable') IS NOT NULL DROP TABLE #TestTable;

SELECT PK, Terminal, MarketDate = CAST(d.MarketDate AS DATETIME)

,d.Prod, d.Cost

INTO #TestTable

FROM (

SELECT 1, 1, '2014-12-01 15:04:00.000','A', 2.23...

peter.cox (12/11/2014)

---

Sorry! Been ages.

It was just a business requirement to search a free form field. Obviously with that being the case, people have the ability to put the data in...

Try this for a size

😎

declare @storedprocedure_name nvarchar(30) = 'my_proc_name';

SELECT N'USE ' + DB_NAME() + NCHAR(59) + NCHAR(13) + NCHAR(10) +

N'IF EXISTS (SELECT * FROM sysobjects...

Has the appeal of Cusano Rojo:rolleyes:,but it works

😎

Alan.B (12/10/2014)

---

I'm curious (general question to everyone)...

Wouldn't this be faster?

-- the function

CREATE FUNCTION dbo.DIGITSONLYAB(@pstring varchar(8000))

RETURNS TABLE WITH SCHEMABINDING AS

RETURN SELECT ISALLDIGITS = CASE PATINDEX('%[^0-9]%',@pstring) WHEN 0 THEN 1...

djj (12/10/2014)

---

Thanks Eirikur. just one question, what does "& 0x7FFF" do? I realize that the & is for "bitwise and", however I am not very good at...

I know it´s not perfect, the dynamic is just to contain the whole thing in a single batch, took it from an old code of mine which took the table...

Good catch, never tested the functions for a null value:pinch:, here is a revised version

😎

/* Eirikur's modification of Jeff's Modification of Erikur's Function */

GO

IF OBJECT_ID('dbo.DigitsOnlyEE') IS NOT NULL DROP FUNCTION...