Please help me to write the sql

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 2005 Forums

Transact-SQL (2005)

Please help me to write the sql

Author

Topic

lulu2792
Starting Member

12 Posts

Posted - 2010-06-01 : 21:22:37

Hello everyone, I am a new with T-SQL. So, please help me to write the sql.

I have table Price (Code column is primary column):


Code    Value
A1       234
A2       525 
A3       566

I will input a string and the sql need to return a table.

Ex1: input 'A2' -> return:


Code    Value
A2       525

Ex2: input 'A1 A3' -> return:


Code    Value
A1       234
A3       566

Ex3: input 'A1 A3 A1' -> return:


Code    Value
A1       234
A3       566

Ex4: input 'A1 A4' -> return:


Code    Value
A1       234

Please help me. Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-02 : 03:47:35

[code]--select max(datum) from post100.skeppningsstatistikdetaljer

DECLARE @Sample TABLE
(
Code CHAR(2) NOT NULL,
Value SMALLINT NOT NULL
)

INSERT @Sample
SELECT 'A1', 234 UNION ALL
SELECT 'A2', 525 UNION ALL
SELECT 'A3', 566

DECLARE @Search VARCHAR(MAX) = 'A2 A1',
@xml XML

SET @XML = CAST('<x>' + REPLACE(@Search, ' ', '</x><x>') + '</x>' AS XML)

SELECT s.Code,
s.Value
FROM @Sample AS s
INNER JOIN (
SELECT w.value('.', 'CHAR(2)')
FROM @XML.nodes('x') AS n(w)
) AS x(Code) ON x.Code = s.Code[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources