Answered by:
SQL Query : Substring function

Question
-
Hi guys - How do i use the substring function and insert values into a column in sql table.
Below is the table structure:
Create Table dbo.letterfileNames
(FileID int identity(1,1)
, FileName Varchar(max)
, FileFullPath varchar(max)
,PatientID varchar (100)
, Createdatetime datetime default getdate()
,LetterID Int
)
Below are the values in the table. I want to insert the value in between first "_" and second "_" into the column LetterID.
Thank you
Answers
-
Create Table dbo.letterfileNames (FileID int identity(1,1) , FileName Varchar(max) , FileFullPath varchar(max) ,PatientID varchar (100) , Createdatetime datetime default getdate() ,LetterID Int ) Insert into dbo.letterfileNames( FileName) values( 'asdf_137_123456789.pdf') , ('App Denied_10_123456789.pdf') select parsename(replace(Filename,'_','.'),3) --or ,Substring(FileName, charindex('_',FileName)+1,len(FileName)) ,Stuff(Substring(FileName, charindex('_',FileName)+1,len(FileName)) ,Charindex('_',Substring(FileName, charindex('_',FileName)+1,len(FileName))),len(FileName),'') from dbo.letterfileNames drop Table dbo.letterfileNames
- Edited by Jingyang LiModerator Thursday, July 18, 2019 8:09 PM
- Proposed as answer by Sabrina ZhangMicrosoft contingent staff Monday, July 22, 2019 8:08 AM
- Marked as answer by kkran Monday, July 22, 2019 3:05 PM
-
Hi kkran,
Here we go:
DECLARE @letterfileNames TABLE( FileID INT IDENTITY(1,1) , [FileName] VARCHAR(100) , LetterID INT ) INSERT INTO @letterfileNames( FileName) VALUES ('Annual Cap Met_137_123366_20190716.pdf') , ('App Denied_10_139018_20190716.pdf') -- before SELECT * FROM @letterfileNames; UPDATE @letterfileNames SET LetterID = CAST(PARSENAME(REPLACE(REPLACE([Filename],'.','~'),'_','.'), 3) AS INT); -- after SELECT * FROM @letterfileNames;
Output:
FileID FileName LetterID 1 Annual Cap Met_137_123366_20190716.pdf 137 2 App Denied_10_139018_20190716.pdf 10
- Edited by Yitzhak Khabinsky Thursday, July 18, 2019 8:49 PM
- Proposed as answer by Sabrina ZhangMicrosoft contingent staff Monday, July 22, 2019 8:08 AM
- Marked as answer by kkran Monday, July 22, 2019 3:05 PM
-
Hi kkran,
Here's the code you can refer to:
Create Table dbo.letterfileNames (FileID int identity(1,1) , FileName Varchar(max) , FileFullPath varchar(max) ,PatientID varchar (100) , Createdatetime datetime default getdate() ) go Insert into dbo.letterfileNames (Filename) values('Annual Cap Met_137_123366_20190716070513.pdf'), ('App Denied_10_139018_20190716070513.pdf'), ('App Denied_10_158480_20190716070513.pdf') select FileID,[FileName],FileFullPath,PatientID,Createdatetime, substring(name1,1,CHARINDEX('_', name1)-1) as [LetterID] from (select *, SUBSTRING([filename],charindex('_',[filename])+1, CHARINDEX('_', [filename], 0) + 1) as [name1] from dbo.letterfileNames) as a /* FileID FileName FileFullPath PatientID Createdatetime LetterID ----------- ----------------------------------------------- -------------- ----------- ----------------------- ----------- 1 Annual Cap Met_137_123366_20190716070513.pdf NULL NULL 2019-07-19 10:13:23.773 137 2 App Denied_10_139018_20190716070513.pdf NULL NULL 2019-07-19 10:13:23.773 10 3 App Denied_10_158480_20190716070513.pdf NULL NULL 2019-07-19 10:13:23.773 10 */
Sabrina
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.- Proposed as answer by Sabrina ZhangMicrosoft contingent staff Monday, July 22, 2019 8:08 AM
- Marked as answer by kkran Monday, July 22, 2019 3:05 PM
All replies
-
Create Table dbo.letterfileNames (FileID int identity(1,1) , FileName Varchar(max) , FileFullPath varchar(max) ,PatientID varchar (100) , Createdatetime datetime default getdate() ,LetterID Int ) Insert into dbo.letterfileNames( FileName) values( 'asdf_137_123456789.pdf') , ('App Denied_10_123456789.pdf') select parsename(replace(Filename,'_','.'),3) --or ,Substring(FileName, charindex('_',FileName)+1,len(FileName)) ,Stuff(Substring(FileName, charindex('_',FileName)+1,len(FileName)) ,Charindex('_',Substring(FileName, charindex('_',FileName)+1,len(FileName))),len(FileName),'') from dbo.letterfileNames drop Table dbo.letterfileNames
- Edited by Jingyang LiModerator Thursday, July 18, 2019 8:09 PM
- Proposed as answer by Sabrina ZhangMicrosoft contingent staff Monday, July 22, 2019 8:08 AM
- Marked as answer by kkran Monday, July 22, 2019 3:05 PM
-
Hi kkran,
Here we go:
DECLARE @letterfileNames TABLE( FileID INT IDENTITY(1,1) , [FileName] VARCHAR(100) , LetterID INT ) INSERT INTO @letterfileNames( FileName) VALUES ('Annual Cap Met_137_123366_20190716.pdf') , ('App Denied_10_139018_20190716.pdf') -- before SELECT * FROM @letterfileNames; UPDATE @letterfileNames SET LetterID = CAST(PARSENAME(REPLACE(REPLACE([Filename],'.','~'),'_','.'), 3) AS INT); -- after SELECT * FROM @letterfileNames;
Output:
FileID FileName LetterID 1 Annual Cap Met_137_123366_20190716.pdf 137 2 App Denied_10_139018_20190716.pdf 10
- Edited by Yitzhak Khabinsky Thursday, July 18, 2019 8:49 PM
- Proposed as answer by Sabrina ZhangMicrosoft contingent staff Monday, July 22, 2019 8:08 AM
- Marked as answer by kkran Monday, July 22, 2019 3:05 PM
-
Hi kkran,
Here's the code you can refer to:
Create Table dbo.letterfileNames (FileID int identity(1,1) , FileName Varchar(max) , FileFullPath varchar(max) ,PatientID varchar (100) , Createdatetime datetime default getdate() ) go Insert into dbo.letterfileNames (Filename) values('Annual Cap Met_137_123366_20190716070513.pdf'), ('App Denied_10_139018_20190716070513.pdf'), ('App Denied_10_158480_20190716070513.pdf') select FileID,[FileName],FileFullPath,PatientID,Createdatetime, substring(name1,1,CHARINDEX('_', name1)-1) as [LetterID] from (select *, SUBSTRING([filename],charindex('_',[filename])+1, CHARINDEX('_', [filename], 0) + 1) as [name1] from dbo.letterfileNames) as a /* FileID FileName FileFullPath PatientID Createdatetime LetterID ----------- ----------------------------------------------- -------------- ----------- ----------------------- ----------- 1 Annual Cap Met_137_123366_20190716070513.pdf NULL NULL 2019-07-19 10:13:23.773 137 2 App Denied_10_139018_20190716070513.pdf NULL NULL 2019-07-19 10:13:23.773 10 3 App Denied_10_158480_20190716070513.pdf NULL NULL 2019-07-19 10:13:23.773 10 */
Sabrina
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.- Proposed as answer by Sabrina ZhangMicrosoft contingent staff Monday, July 22, 2019 8:08 AM
- Marked as answer by kkran Monday, July 22, 2019 3:05 PM
-