none
SQL Query : Substring function RRS feed

  • 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
    Thursday, July 18, 2019 8:00 PM

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 


    Thursday, July 18, 2019 8:08 PM
    Moderator
  • 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

    Thursday, July 18, 2019 8:48 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.

    Friday, July 19, 2019 2:17 AM

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 


    Thursday, July 18, 2019 8:08 PM
    Moderator
  • 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

    Thursday, July 18, 2019 8:48 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.

    Friday, July 19, 2019 2:17 AM
  • Thank you everyone
    Monday, July 22, 2019 3:06 PM