Finding a column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Finding a column

Author

Topic

dolness
Starting Member

1 Post

Posted - 2007-03-05 : 11:07:44

I have a table with multiple fields like p1, p2, p3, p4, p5. For other reasons I can't denormize it. I need to find the name of the column that contains a certain number. It could be any, but only one for each record. Someone please help.

Select #field name# where '2324' in (p1, p2, p3, p4, p5)

D.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-03-05 : 12:07:29

[code]
DECLARE @t TABLE (p1 INT, p2 INT, p3 INT, p4 INT)
INSERT INTO @t
SELECT 1, 2, 3, 4 UNION ALL
SELECT 5, 6, 7, 8 UNION ALL
SELECT 9, 10, 11, 12 UNION ALL
SELECT 13, 14, 15, 16

DECLARE @searchNum INT
SELECT @searchNum = 5

SELECT CASE
WHEN p1%@searchNum = 0 THEN 'p1'
WHEN p2%@searchNum = 0 THEN 'p2'
WHEN p3%@searchNum = 0 THEN 'p3'
WHEN p4%@searchNum = 0 THEN 'p4'
END AS colName
FROM @t
WHERE @searchNum IN (p1, p2, p3, p4)
[/code]

Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-05 : 13:43:29

mladen -- Why are you using

A%B=0

instead of

A=B

?? Won't that potentially return lots of false positives? Or am I missing something?

- Jeff
http://weblogs.sqlteam.com/JeffS

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-03-05 : 13:47:03

yeah you're right...

i was doing someting with % right before
so i automaticaly used it here also.
haven't switched the thought process yet...

Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources