I have to ask why you'd resort to a UDF (not to mention calling it twice!) when it is quite straightforward to simply strip off the leading number as follows:

WITH...

I suspect there's probably more business rules at play here than my simplistic solution addresses, but maybe it will give you an idea.

SELECT *

,UNIQUE_ID=CASE ACTIVITY_ID WHEN...

I can't say I really understand your requirements enough to give it a shot.

For example, shouldn't this row be spit out of your expected results because 8 is not between...

If anybody cares, here's my vote:

WITH CTE AS (

SELECT Field03

FROM #TestTable

WHERE Field03 LIKE '0%')

UPDATE CTE

SET...

You're welcome!

Articles abound on the FOR XML PATH...

Perhaps something like this?

WITH SampleData (EntryID, DateInserted, InsertedBy) AS (

SELECT 1776285,'2013-06-03 07:46:38.340',592

UNION ALL SELECT 1776286,'2013-06-03 07:47:47.677',592

UNION ALL SELECT...

Or perhaps like this?

WITH SampleData (Code, Sub_code, Name, Amounts) AS (

SELECT 'Dir',20,'Main_dir',100

UNION ALL SELECT 'Rei',20,'Main_Rei',50

UNION ALL SELECT 'Dir',50,'Withhold_dir',10

...

I didn't realize that brute force was allowed.

CREATE TABLE #temp

(set_id INT

,product CHAR(5)

,attribute INT)

INSERT INTO #temp (set_id, product, attribute) VALUES (1,'A',10) ,(1,'A',11)

INSERT INTO #temp (set_id, product, attribute) VALUES (2,'A',10),(2,'A',12)

INSERT INTO #temp...

Perhaps this works for you?

WITH JoinedTables AS (

SELECT a.OriginCode, a.OCID, b.DestID, b.Ordered

FROM LOC a

JOIN DEST b ON a.OCID...

Just to be a little out of control (of the resulting type), you don't even need CONVERT:

CREATE TABLE #Test (TestData varchar(50));

INSERT INTO #Test SELECT '+000000000000760.00';

INSERT INTO #Test SELECT '+000004532078501.60';

INSERT INTO...

And here's another way (that I am less confident of because it requires that the SUM of attributes for a set is unique), but maybe it'll give you some additional...

Not sure this is a particularly efficient way to do this but this might work:

SELECT set_id, product, attribute=CAST(item AS INT)

FROM (

SELECT set_id=MIN(set_id), product, allattributes

...

drew.allen (8/19/2013)

---

Slightly shorter

DECLARE @Sort1 varchar(10)='val3'; --NOTE: @Sort2 ommitted from this example because I am still stuck on @sort1

--DECLARE @Sort1 varchar(10)='val3';

DECLARE @x TABLE (val1 varchar(10) not null, val2 varchar(10) not null,val3...

You might try changing:

ORDER BY (SELECT NULL)

To ORDER BY the column with the most entries.

My aversion to Carpal Tunnel insists that I suggest this:

DECLARE @Sort1 varchar(10)='val3'; --NOTE: @Sort2 ommitted from this example because I am still stuck on @sort1

--DECLARE @Sort1 varchar(10)='val3';

DECLARE @x TABLE (val1...