The middle value of how many values? Will it always be three?

Looks like this is going to...

something like this?

DROP TABLE #buildings

CREATE TABLE #buildings (building int, unit varchar(4), sort_order int)

INSERT INTO #buildings (building, unit, sort_order)

SELECT 1, '101', 1 UNION ALL

SELECT 1, '102', 2 UNION ALL

SELECT 1, '103',...

You're welcome Chris, thanks for the feedback!

Hi Chris

IF is for conditional execution of statements within a batch. Use CASE for conditions within a statement. Check out CASE in BOL, there are two types, simple and searched....

Hey Matt, see also

http://www.sqlservercentral.com/Forums/Topic565548-8-1.aspx?Update=1

Cheers

ChrisM

Hi Chris

It's a kinda cross-post, Matt is dealing with it too:

http://www.sqlservercentral.com/Forums/Topic565481-8-1.aspx

Cheers

ChrisM

Could be significant with OP's data too:

SELECT note,

PATINDEX('%,[0-9][0-9],',note) AS A,

PATINDEX('%[0-9][0-9]',note) AS B,

...

Does it work? What's it supposed to do? It doesn't return results where ',[0-9][0-9],' is in the rightmost part of the column...

SELECT note,

PATINDEX('%,[0-9][0-9],',note) AS A,

PATINDEX('%[0-9][0-9]',note) AS B,

SUBSTRING(note, PATINDEX('%[0-9][0-9]',note),...

That's because you're expecting this section...

(SELECT TOP 1 TargetID

FROM ##T2 where TargetID not in...

You could account for the spaces as follows. There's a system proc too:

[font="Courier New"]DECLARE @cVersion VARCHAR(5)

SELECT @cVersion = CASE WHEN UPPER(REPLACE(@@version, '  ', ' ')) LIKE 'MICROSOFT SQL SERVER 2000 %'...

Hi David

@@VERSION:

DECLARE @cVersion VARCHAR(200)

SELECT @cVersion = @@version

SELECT SUBSTRING(@cVersion, 23, 4)

...you could use conditional processing on the result.

Cheers

ChrisM

[font="Courier New"]-- Set up some test data

CREATE TABLE ##T2 (TargetID smallint)

INSERT INTO ##T2

SELECT 1 UNION ALL

SELECT 2 UNION ALL

SELECT 3 UNION ALL

SELECT 4 UNION ALL

SELECT 5 UNION ALL

SELECT 6...

Random - that's the point.

It looks very like a...

Bhuvnesh,

SQL Server detects that the columns are missing before running the code, but doesn't see that the new columns are added to the table in step 2.

However, you don't need...

Something like this?

[font="Courier New"]SELECT CASE WHEN LEFT(strLinesDate, 2) = 'PC'

   THEN REPLACE(strLinesDate,

       LEFT(strLinesDate, 11),

       'P  ' + SUBSTRING(strLinesDate,4,8) + ', ' + 'C  ' + SUBSTRING(strLinesDate,4,8))

   ELSE strLinesDate END...