PATINDEX - 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 2008 Forums

Transact-SQL (2008)

PATINDEX

Author

Topic

JanakiRam
Starting Member

22 Posts

Posted - 2013-06-22 : 03:14:18

Hi.,

Need a query for below mentioned result

DECLARE @Variable VARCHAR(MAX)
SELECT @Variable = '''8888;9999;5555;'',''2222;1111;'
SELECT @Variable

DECLARE @Table TABLE (EmployeeId INT, Name VARCHAR(256))
INSERT INTO @Table
SELECT 8888,'J'
INSERT INTO @Table
SELECT 9999,'A'
INSERT INTO @Table
SELECT 5555,'N'
INSERT INTO @Table
SELECT 2222,'A'
INSERT INTO @Table
SELECT 1111,'K'
INSERT INTO @Table
SELECT 9999,'I'

OUT PUT:

8888,9999
9999,5555
8888,5555
2222,1111
9999,1111

Means N*N Time table have 9 rows then probability have 9*9 = 81 cases needed

JanakiRam

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-22 : 10:51:38

Is the data already in the table, or is it in @Variable as semi-colon separated values? Assuming it is in the table, if you want to get all combintions,

SELECT a.EmployeeId, b.EmployeeId FROM @Table a CROSS JOIN @Table b;

JanakiRam
Starting Member

22 Posts

Posted - 2013-06-24 : 03:23:42

DECLARE @Variable VARCHAR(8000)

SELECT @Variable = '1111,8888,9999,2222,5555'

DECLARE @List TABLE (EmployeeId VARCHAR(8000))
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(',',@Variable,0) <> 0
BEGIN
SELECT
@sItem=RTRIM(LTRIM(SUBSTRING(@Variable,1,CHARINDEX(',',@Variable,0)-1))),
@Variable=RTRIM(LTRIM(SUBSTRING(@Variable,CHARINDEX(',',@Variable,0)+LEN(','),LEN(@Variable))))

IF LEN(@sItem) > 0
INSERT INTO @List SELECT @sItem
END

JanakiRam

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-06-24 : 03:33:21

[code]
DECLARE @Variable VARCHAR(MAX)
SELECT @Variable = '1111,8888,9999,2222,5555'

select v1.stringval, v2.stringval
from CSVTable(@Variable) v1
cross join CSVTable(@Variable) v2
[/code]
get CSVTable from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=CSVTable

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources