Prev: SQL query puzzle
Next: Remove Space within a String
From: John on 4 Dec 2009 11:18
I'm having trouble with a select statement and wondering if some
experts could help:)

I have a VARCHAR field in a table called IDFormula which contains an
INT PRIMARY KEY value from a column in the same table(with brackets
around it). For example if the table was called Unit, and one of the
primary key values was 341, then somewhere in the IDFormula column
there may be a [341] in it as such: [341]+1. I need to select any row
from the table which has a primary key that can be found in an
IDFormula column. I hope this is clear. Here is some example code I
threw together.

DECLARE @Unit TABLE(
UnitID INT PRIMARY KEY IDENTITY(1,1),
IDFormula VARCHAR(5000)
)

INSERT INTO @Unit(IDFormula) VALUES(NULL);
INSERT INTO @Unit(IDFormula) VALUES(NULL);
INSERT INTO @Unit(IDFormula) VALUES('([1]+3)*2)');

SELECT * FROM @Unit;

--This is what I have tried. It never returns anything.
SELECT * FROM @Unit UN
WHERE UN.UnitID IN(SELECT U2.UnitID FROM @Unit U2
WHERE CHARINDEX('[' + LTRIM(RTRIM(UN.UnitID)) + ']',
U2.IDFormula)>0)


From: Plamen Ratchev on 4 Dec 2009 11:40
Here is one method:

SELECT UnitID, IDFormula
FROM @Unit AS U
WHERE EXISTS(SELECT *
FROM @Unit AS U2
WHERE U2.IDFormula LIKE '%![' + CAST(U.UnitID AS VARCHAR(10)) + ']%' ESCAPE '!');

--
Plamen Ratchev
http://www.SQLStudio.com
From: John on 4 Dec 2009 11:49
On Dec 4, 10:40 am, Plamen Ratchev <Pla...(a)SQLStudio.com> wrote:
> Here is one method:
>
> SELECT UnitID, IDFormula
> FROM @Unit AS U
> WHERE EXISTS(SELECT *
>               FROM @Unit AS U2
>               WHERE U2.IDFormula LIKE '%![' + CAST(U.UnitID AS VARCHAR(10)) + ']%' ESCAPE '!');
>
> --
> Plamen Ratchevhttp://www.SQLStudio.com

That works perfectly. Thanks very much!
 | 
Pages: 1
Prev: SQL query puzzle
Next: Remove Space within a String