Sql Query for Table based on start and end character

Question

Book ID	Name	Autor
1	ABC	Manoj
2	BBC	mnoj

Self id	Start character	end car
1	A	C
2	D	I

I have two table Now I want record from first table with Selfid ( second table contains Starting character and End character like in above table all book Started with A,B,C comes with self id 1)

Nitin Sontakke · Answer

Here you go... declare @books table ( [book_id] [ integer ] not null ,[ name ] [ varchar ](100) not null ,[author] [ varchar ](100) not null ) insert into @books values (1, 'ABC' , 'Manoj' ) ,(2, 'EAC' , 'Kiran' ) ,(3, 'BCd' , 'Ravi' ) ,(4, 'HCx' , 'Rahul' ) ,(5, 'JBC' , 'Deepak' ) declare @shelf table ( [shelf_id] [ integer ] not null ,[char_from] [ char ](1) not null ,[char_to] [ char ](1) not null ) insert into @shelf values (1, 'A' , 'C' ) ,(2, 'D' , 'F' ) ,(3, 'G' , 'I' ) ,(4, 'J' , 'L' ) select * from @books b left outer join @shelf s on ascii( substring (b.[ name ], 1, 1)) between ascii(s.[char_from]) and ascii(s.[char_to]) For better performance, you can keep integer columns in shelf table and have it indexed and also an integer column having ascii value of first letter in books table and have it index and use those columns in the query.

Dharmraj Thakur · Answer

Hellow Manoj, You can make multiple condition left join in this situation... create table Books ( BookID int primary key identity(1,1), Name varchar ( max ), Autor varchar ( max ) ) create table Chars ( SelfID int primary key identity(1,1), StartCharacter varchar (1), EndCharacter varchar (1) ) insert into Books( Name ,Autor) values ( 'ABC' , 'Manoj' ) insert into Books( Name ,Autor) values ( 'BBC' , 'mnoj' ) insert into Chars(StartCharacter,EndCharacter) values ( 'A' , 'C' ) insert into Chars(StartCharacter,EndCharacter) values ( 'D' , 'I' ) select bk.*, ch.SelfID from Books bk left join Chars ch on ch.StartCharacter = SUBSTRING (bk. Name , 1, 1) and ch.EndCharacter = SUBSTRING (bk. Name , LEN(bk. Name ), 1)