Thishave , after 3 characterwould indicate a VARCHAR or CHAR datatype. Check the datatypes of the columns in the tables. Try casting to INT or BIGINT the column which has...

Sure:

DROP TABLE #T1

CREATE TABLE #T1 (PersonId VARCHAR(2), OID VARCHAR(2))

INSERT INTO #T1 (PersonId, OID)

SELECT 'P1', 'O1' UNION ALL

SELECT 'P2', 'O7' UNION ALL

SELECT 'P3', 'O1'

SELECT a.PersonId, (SELECT OID FROM #T1 WHERE PersonId...

Don't forget a tally-table version!

DECLARE @Untrimmed varchar(50), @Trimmed varchar(50)

SET @Untrimmed = 'HaveNow!/7896-7'

SET @Untrimmed = 'HaveNow'

--

SELECT @Trimmed = LEFT(@Untrimmed, (SELECT ISNULL(MIN(n.number)-1, LEN(@Untrimmed))

FROM Numbers n

WHERE n.number <= LEN(@Untrimmed)

AND NOT ascii(lower(substring(@Untrimmed, n.number,1)))...

karthur (4/9/2009)

---

I have a column with 'name/dept' or 'name-dept', such as 'jones/1234' or 'jones-1234'.

Is there a function or functions I can use in SQL Server 2005 to give me just...

No worries Allan, though I think it's Adi who deserves the credit.

Have a read of this when you've got a few minutes.

Cheers

ChrisM

The BEGIN is in the wrong place. Here's the example from BOL:

DECLARE Employee_Cursor CURSOR FOR

SELECT EmployeeID, Title FROM AdventureWorks.HumanResources.Employee;

OPEN Employee_Cursor;

FETCH NEXT FROM Employee_Cursor;

WHILE @@FETCH_STATUS = 0

BEGIN

...

Do you mean this?

DROP TABLE #GroupMembers

CREATE TABLE #GroupMembers (

Group_member_id INT,

Member_name VARCHAR(10),

Group_name VARCHAR(3),

[Order] INT)

...

john.arnott (4/8/2009)

---

OK. If the puzzle's rules specify that the given data always has increasing values in the respective columns of each row, so be it. Does that affect...

RBarryYoung (4/8/2009)

---

Peso (4/8/2009)

---

I agree with most points, but I strongly disagree with creating sample data that fulfills OP's wanted output. I don't believe in some divine intervention that sample data...

No fair :sick: it's only 25% populated with numbers! If that!

Peso (4/8/2009)

---

Ramesh (4/8/2009)

---

See these results, the row 5 should be followed after row 4 as the col2 value of row 5 is higher than that of row 4.

-- Actual Results

ID...

Here's a randomised set of 15 rows, Peso - wanna give them both a try?...

Ooookaaaaayyyy proof of the pudding...run against randomised...

36376 ms? What are you running this test on, Ramesh - a twin-floppy 80's luggable? 😀

876 ms is awesome:cool:

Very elegant! Makes my CASEy stuff look like a dustbin full of spaghetti!

Who's gonna test with 40,000 rows then? 😀