If you want/have to do it in a Derived Column you can use the following expressions:

Weight: RIGHT("000" + (DT_STR,3,1250)Weight,3)

Order: SUBSTRING((DT_STR,9,1250)Order + "000000000",1,9)

Code: RIGHT("00" + (DT_STR,3,1250)Code,2)

Peter

Actually, it can, although in an indirect way:

CREATE PROCEDURE dbo.Test1 AS

BEGIN TRY

...

GilaMonster (11/12/2011)

---

Peter Brinkhaus (11/12/2011)

---

I'm probably not using the correct terminology, but let's give it a shot. To summarize, procedure Test1 contains a compile-time error (unknown table), while procedure Test2 contains...

I'm probably not using the correct terminology, but let's give it a shot. To summarize, procedure Test1 contains a compile-time error (unknown table), while procedure Test2 contains a runtime-error (division...

Sorry for...

SQL Kiwi (11/12/2011)

---

Peter Brinkhaus (11/12/2011)

---

I always thought "page splits=fragmentation", but on SQL 2005 the fragmentation is less then on SQL 2008, if only 0.38% vs. 0.43%. But you're right, the...

SQL Kiwi (11/12/2011)

---

SQL Server 2008 does a better job of avoiding page splits during the INSERT to the clustered index. Issue an ALTER INDEX ALL ON dbo.Metatags REBUILD on...

Now that's a nice little trick to remember, Paul. Very impressive improvement. As a side note, I just run the script on both SQL 2005 and 2008 and noticed that...

drew.allen (11/11/2011)

---

Even so, the performance may still be better using the string splitter than without. The only way to now for sure is thorough testing, which is why I...

drew.allen (11/11/2011)

---

Peter Brinkhaus (11/11/2011)

---

drew.allen (11/11/2011)

---

Your solution is not SARGable. ...

drew.allen (11/11/2011)

---

Your solution is not SARGable. If you care...

Lowell (11/11/2011)

---

a lot of the work around examples so far would return false positives if the values are two or more digits, thought right? 2|5|21|23|55 for example

redesign, or split the...

Dev (11/11/2011)

---

Peter Brinkhaus (11/11/2011)

---

Agreed with Dev, but you can do it with a LIKE:

SELECT

*

FROM

(

SELECT '2|4|6' -- '3|24|4'

) SampleTable(ID)

WHERE

'|' + ID + '|'...

Agreed with Dev, but you can do it with a LIKE:

SELECT

*

FROM

(

SELECT '2|4|6'

) SampleTable(ID)

WHERE

'|' + ID + '|' LIKE '%|2|%'

As an alternative

SELECT

DATEDIFF(S, 0, CAST('1900-01-01T' + LEFT('00:00:00', 8 - LEN(AnyTime)) + AnyTime AS DATETIME))

FROM

(

SELECT '1' UNION ALL -- 1 second

SELECT '15' UNION ALL --...