Sign in
 

none
SQL Query : Substring function RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    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

  • Question
    Sign in to vote
    0
    Sign in to vote 
    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote 
    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thank you everyone
    Monday, July 22, 2019 3:06 PM
    |