how to search and display record from table ?

Bhavesh Vankar

4y856

in my form having three textboxes for First Name, Middle Name, Last Name, and some textboxes for other details to store in db.

now if user enter firstname and last name in textbox and enter name is blocked in application so one popup page should be open and should be display record like, name, address, mono, and photo.

i tried to solve this but issue is the query should be validate probality for blocked person. like if first name's 4 character and last name's 4 character's probality should be validate...

second issue is the name is stored in single column in table.

after popup open user should move record next or preveous to check propbality of blocked persons in application.

i tryied as below...

how to make query to find probality of blocked person for first 4 and last 4 character from table....

query += "SELECT * FROM (";
        query += "    SELECT ROW_NUMBER() OVER(ORDER BY ID) AS 'RowNo',";
        query += "        NAME,ADDRESS,CITYORVILLAGE,TALUKA,DISTRICT,CONTACTNO,(SELECT COUNT(*) FROM RESTICTIED_PERSONS) AS 'Total'";
        query += "    FROM RESTICTIED_PERSONS";
        query += ") t ";
        query += "WHERE t.RowNo = @Row";

3 Replies

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

Muhammad Imran Ansari4y ago
Accepted answer
''@Bhavesh,
Always request normalized data to ensure the highest data quality.
First Name: William
Middle Name: John
Last Name: Sain
In SQL, there is only one column with name FullName having data 'William John Sain'
Below is query to split FullName into First, Middle and Last name.
SELECT
LTRIM(SUBSTRING(FullName,1,ISNULL(NULLIF(CHARINDEX(' ',FullName),0),1000))) AS FirstName,
LTRIM(SUBSTRING(FullName, CHARINDEX(' ',FullName),
CASE WHEN (CHARINDEX(' ',FullName,CHARINDEX(' ',FullName)+1)-CHARINDEX(' ',FullName))<=0 THEN 0
ELSE CHARINDEX(' ',FullName,CHARINDEX(' ',FullName)+1)-CHARINDEX(' ',FullName) END)) AS MiddleName,

LTRIM(SUBSTRING(FullName,ISNULL(NULLIF(CHARINDEX(' ',FullName,CHARINDEX(' ',FullName)+1),0),CHARINDEX(' ',FullName)),
CASE WHEN CHARINDEX(' ',FullName)=0 THEN 0 ELSE LEN(FullName) END)) AS LastName

FROM Person
WHERE id = 1
I assume space ' ' between the full name. If there is any other character you are using, just place ' ' with that character in CHARINIDEX first parameter.
Bhavesh Vankar4y ago
The name is stored in table in single column. but in form there are three textboxes like Fist name, Middle name, Last name... while saving the this three textboxes string is concating and storing in single column in table...
so main issue is how to split and fetch it from db.
Muhammad Imran Ansari4y ago
You can use LEFT and RIGHT functions to get the name first 4 and last characters. change your query accordingly.
Example:
First Name: William
Last Name: John
In your case, first and last name is storing in 'Name' field. Name: William John
SELECT LEFT(Name, 4) FirstFour, RIGHT(Name, 4) AS LastFour;

SELECT ROW_NUMBER() OVER(ORDER BY ID) AS 'RowNo',
NAME,ADDRESS,CITYORVILLAGE,TALUKA,DISTRICT,CONTACTNO,(SELECT COUNT(*) FROM RESTICTIED_PERSONS) AS 'Total'
FROM RESTICTIED_PERSONS
WHERE LEFT(Name, 4) = @FirstFour AND RIGHT(Name, 4) = @LastFour