Cross join exclude data based on partial character of a data (Use of RIGHT(RTRIM(xxx,x))

DROP TABLE [dbo].[vwCourseClass]
GO

CREATE TABLE [dbo].[vwCourseClass](
[CourseTitle] [nchar](10) NULL,
[ClassTitle] [nchar](10) NULL
) ON [PRIMARY]

GO

INSERT INTO [dbo].[vwCourseClass] ([CourseTitle] ,[ClassTitle]) VALUES('F;BUA','F;BUA;A1')
INSERT INTO [dbo].[vwCourseClass] ([CourseTitle] ,[ClassTitle]) VALUES('F;BUA','F;BUA;E1')
INSERT INTO [dbo].[vwCourseClass] ([CourseTitle] ,[ClassTitle]) VALUES('F;CHA','F;CHA;A1')
INSERT INTO [dbo].[vwCourseClass] ([CourseTitle] ,[ClassTitle]) VALUES('F;CHA','F;CHA;B1')
GO

SELECT
vwCourseClass.ClassTitle
, vwCourseClass_1.ClassTitle
FROM
vwCourseClass
,vwCourseClass AS vwCourseClass_1
WHERE
(
((vwCourseClass.CourseTitle)='F;BUA')
And
((vwCourseClass_1.CourseTitle)='F;CHA' )
)

GO

Output:

F;BUA;A1 F;CHA;A1
F;BUA;E1 F;CHA;A1
F;BUA;A1 F;CHA;B1
F;BUA;E1 F;CHA;B1

*********************************************************************************************

Expected Output:
Now I would like to update the SQL Input so that I will get the output as:

F;BUA;A1 F;CHA;B1
F;BUA;E1 F;CHA;A1
F;BUA;E1 F;CHA;B1

Means: I would like to explude the combination where last 2 character match. Here ( F;BUA;A1 F;CHA;A1 ) in both data “A1” is same. So this data will exclude.

*********************************************************************************************

Here “course titles” is defining as NCHAR, which means they are padded with blanks on the right to the specified length. NVARCHAR might be a better choice, but you can trim the blanks using RTRIM().

*********************************************************************************************

Solution: (Reff http://www.sqlservercentral.com/Forums/Topic1118806-392-1.aspx)

 

SELECT
vwCourseClass.ClassTitle
, vwCourseClass_1.ClassTitle
FROM
vwCourseClass
,vwCourseClass AS vwCourseClass_1
WHERE
(
((vwCourseClass.CourseTitle)='F;BUA')
And
((vwCourseClass_1.CourseTitle)='F;CHA' )
)
AND RIGHT(RTRIM(vwCourseClass.ClassTitle),2) <> RIGHT(RTRIM(vwCourseClass_1.ClassTitle),2)

*********************************************************************************************

———————————————————————————————————————————-

Here “course titles” is defining as NVARCHAR  then solution could be:

AND RIGHT(dbo_vwCourseClass.ClassTitle,2) <> RIGHT(dbo_vwCourseClass_1.ClassTitle,2)

———————————————————————————————————————————-

*********************************************************************************************

Working with large data Example:

DROP TABLE [dbo].[vwCourseClass]
GO

CREATE TABLE [dbo].[vwCourseClass](
[CourseTitle] [nchar](10) NULL,
[ClassTitle] [nchar](10) NULL
) ON [PRIMARY]

GO

INSERT INTO [dbo].[vwCourseClass] ([CourseTitle] ,[ClassTitle]) VALUES('F;BUA','F;BUA;A1')
INSERT INTO [dbo].[vwCourseClass] ([CourseTitle] ,[ClassTitle]) VALUES('F;BUA','F;BUA;E1')
INSERT INTO [dbo].[vwCourseClass] ([CourseTitle] ,[ClassTitle]) VALUES('E;BIA','E;BIA;A1')
INSERT INTO [dbo].[vwCourseClass] ([CourseTitle] ,[ClassTitle]) VALUES('E;BIA','E;BIA;B1')
INSERT INTO [dbo].[vwCourseClass] ([CourseTitle] ,[ClassTitle]) VALUES('F;CHA','F;CHA;A1')
INSERT INTO [dbo].[vwCourseClass] ([CourseTitle] ,[ClassTitle]) VALUES('F;CHA','F;CHA;E1')
INSERT INTO [dbo].[vwCourseClass] ([CourseTitle] ,[ClassTitle]) VALUES('F;CHA','F;CHA;F1')
INSERT INTO [dbo].[vwCourseClass] ([CourseTitle] ,[ClassTitle]) VALUES('1;REX','1;REX;A1')
INSERT INTO [dbo].[vwCourseClass] ([CourseTitle] ,[ClassTitle]) VALUES('1;REX','1;REX;B1')
INSERT INTO [dbo].[vwCourseClass] ([CourseTitle] ,[ClassTitle]) VALUES('1;REX','1;REX;A1')
INSERT INTO [dbo].[vwCourseClass] ([CourseTitle] ,[ClassTitle]) VALUES('1;REX','1;REX;B1')
INSERT INTO [dbo].[vwCourseClass] ([CourseTitle] ,[ClassTitle]) VALUES('1;REX','1;REX;C1')
INSERT INTO [dbo].[vwCourseClass] ([CourseTitle] ,[ClassTitle]) VALUES('1;REX','1;REX;D1')
INSERT INTO [dbo].[vwCourseClass] ([CourseTitle] ,[ClassTitle]) VALUES('1;REX','1;REX;E1')
INSERT INTO [dbo].[vwCourseClass] ([CourseTitle] ,[ClassTitle]) VALUES('1;REX','1;REX;F1')
GO

SELECT
vwCourseClass.ClassTitle
, vwCourseClass_1.ClassTitle
, vwCourseClass_2.ClassTitle
, vwCourseClass_3.ClassTitle
FROM
vwCourseClass
,vwCourseClass AS vwCourseClass_1
,vwCourseClass AS vwCourseClass_2
,vwCourseClass AS vwCourseClass_3
WHERE
(
((vwCourseClass.CourseTitle)='F;BUA')
And
((vwCourseClass_1.CourseTitle)='F;CHA' )
And
((vwCourseClass_2.CourseTitle)='E;BIA' )
And
((vwCourseClass_3.CourseTitle)='1;REX' )

AND 
(RIGHT(RTRIM(vwCourseClass.ClassTitle),2) <> RIGHT(RTRIM(vwCourseClass_1.ClassTitle),2) ) 
AND 
(RIGHT(RTRIM(vwCourseClass.ClassTitle),2) <> RIGHT(RTRIM(vwCourseClass_2.ClassTitle),2) ) 
AND 
(RIGHT(RTRIM(vwCourseClass.ClassTitle),2) <> RIGHT(RTRIM(vwCourseClass_3.ClassTitle),2) ) 
AND 
(RIGHT(RTRIM(vwCourseClass_1.ClassTitle),2) <> RIGHT(RTRIM(vwCourseClass_2.ClassTitle),2) ) 
AND 
(RIGHT(RTRIM(vwCourseClass_1.ClassTitle),2) <> RIGHT(RTRIM(vwCourseClass_3.ClassTitle),2) ) 
AND 
(RIGHT(RTRIM(vwCourseClass_2.ClassTitle),2) <> RIGHT(RTRIM(vwCourseClass_3.ClassTitle),2) ) 


)

GO

 


					
Advertisements
By simplemsexchange Posted in SQL 2008

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s