Sql Query for Table based on start and end character

manoj singh

8y829SQL

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)

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke8y ago
Accepted 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.
+1
Dharmraj Thakur8y ago
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)
+1