How to intercept the fields I want from my table

Question

Hi,experts

I have many columns in my table and the data in one column is very verbose, how can I truncate the data I want?
What are the more commonly used string functions in sqlserver?
Thanks,any suggestion will do

I have three columns in my table, one of which contains the student's name and date and the type of assignment submitted, and I need to split them out and find the highest score for their assignment. I have pasted my table structure and the results I want below, thanks in advance guys.

CREATE TABLE Homeworkdetails(
[id] int not null,
[HomeworkName] varchar(50),
[Score] [int]
)

INSERT into Homeworkdetails([id], [HomeworkName], [Score]) VALUES
(1,'Lynn_20220913.doc', 70),
(2,'Lynn(2)_20220914.doc',85),
(3,'Denial_20220913.txt',85),
(4,'Denial_and_Gullen20220914.doc',100),
(5,'Gullen_20220914.txt',88),
(6,'Gullen(2)_20220914.txt',90),
(7,'Lynn_and_Gullen20220914.txt',70),
(8,'Danking_20220912.txt',85),
(9,'Danking(2)_20220913.txt',90)

name date type score
Lynn 2022-09-14 doc 85
Denial 2022-09-13 txt 85
Denial_and_Gullen 2022-09-14 doc 100
Gullen 2022-09-14 txt 90
Lynn_and_Gullen 2022-09-14 txt 70
Danking 2022-09-13 txt 90

Answer 1

Hi @Marksdasjkv ,

Try this code:

CREATE TABLE Homeworkdetails(  
[id] int not null,  
[HomeworkName] varchar(50),  
[Score] [int]  
)  
  
INSERT into Homeworkdetails([id], [HomeworkName], [Score]) VALUES  
(1,'Lynn_20220913.doc', 70),  
(2,'Lynn(2)_20220914.doc',85),  
(3,'Denial_20220913.txt',85),  
(4,'Denial_and_Gullen20220914.doc',100),  
(5,'Gullen_20220914.txt',88),  
(6,'Gullen(2)_20220914.txt',90),  
(7,'Lynn_and_Gullen20220914.txt',70),  
(8,'Danking_20220912.txt',85),  
(9,'Danking(2)_20220913.txt',90)  
  
name date type score  
Lynn 2022-09-14 doc 85  
Denial 2022-09-13 txt 85  
Denial_and_Gullen 2022-09-14 doc 100  
Gullen 2022-09-14 txt 90  
Lynn_and_Gullen 2022-09-14 txt 70  
Danking 2022-09-13 txt 90  
  
  
  
;with cte as   
(  
select *   
,REPLACE(RTRIM(REPLACE(SUBSTRING([HomeworkName],0,patindex('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',[HomeworkName])),'_',' ')),' ','_') +'('   as [name]  
,CAST(SUBSTRING([HomeworkName],patindex('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',[HomeworkName]),8) as date) as [date]  
,SUBSTRING([HomeworkName],CHARINDEX('.',[HomeworkName])+1,LEN([HomeworkName])-CHARINDEX('.',[HomeworkName]))as [type]  
,score as [score2]   
from Homeworkdetails  
)  
,cte2 as (  
select SUBSTRING([name],0,CHARINDEX('(',[name])) as [name]  
,[date]  
,[type]  
,[score] from cte  
)  
,cte3 as (  
select [name]  
,[date]  
,[type]  
,[score]  
,ROW_NUMBER()over(partition by name order by score desc) as nn from cte2  
)select [name],[date],[type],[score] from cte3 where nn=1  

Best regards
Niko

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

You can use the substring function:

   SELECT col1, col2, substring(longcolumn, 1, 200), col4, co5, ...  
   FROM   tbl  
   WHERE ...