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)

Answer 1

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.

 

Answer 2

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)