I have a string:
DECLARE @UserComment AS VARCHAR(1000) = 'bjones marked inspection on system UP for site COL01545 as Refused to COD won''t pay upfront :Routeid: 12 :Inspectionid: 55274'
Is there a way for me to extract everything from the string after 'Inspectionid: ' leaving me just the InspectionID to save into a variable?
Your example doesn't quite work correctly. You defined your variable as varchar(100) but there are more characters in your string than that.
This should work based on your sample data.
DECLARE @UserComment AS VARCHAR(1000) = 'bjones marked inspection on system UP for site COL01545 as Refused to COD won''t pay upfront :Routeid: 12 :Inspectionid: 55274'
select right(@UserComment, case when charindex('Inspectionid: ', @UserComment, 0) > 0 then len(@UserComment) - charindex('Inspectionid: ', @UserComment, 0) - 13 else len(@UserComment) end)
I would do this as:
select stuff(@UserComment, 1, charindex(':Inspectionid: ', @UserComment) + 14, '')
This works even if the string is not found -- although it will return the whole string. To get an empty string in this case:
select stuff(@UserComment, 1, charindex(':Inspectionid: ', @UserComment + ':Inspectionid: ') + 14, '')
Firstly, let me say that your @UserComment variable is not long enough to contain the text you're putting into it. Increase the size of that first.
The SQL below will extract the value:
DECLARE @UserComment AS VARCHAR(1000); SET @UserComment = 'bjones marked inspection on system UP for site COL01545 as Refused to COD won''t pay upfront :Routeid: 12 :Inspectionid: 55274'
DECLARE @pos int
DECLARE @InspectionId int
DECLARE @IdToFind varchar(100)
SET @IdToFind = 'Inspectionid: '
SET @pos = CHARINDEX(@IdToFind, @UserComment)
IF @pos > 0
BEGIN
SET @InspectionId = CAST(SUBSTRING(@UserComment, @pos+LEN(@IdToFind)+1, (LEN(@UserComment) - @pos) + 1) AS INT)
PRINT @InspectionId
END
You could make the above code into a SQL function if necessary.
If the Inspection ID is always 5 digits then the last argument for the Substring function (length) can be 5, i.e.
SELECT SUBSTRING(@UserComment,PATINDEX('%Inspectionid:%',@UserComment)+14,5)
If the Inspection ID varies (but is always at the end - which your question slightly implies), then the last argument can be derived by subtracting the position of 'InspectionID:' from the overall length of the string. Like this:
SELECT SUBSTRING(@UserComment,PATINDEX('%Inspectionid:%',@UserComment)+14,LEN(@usercomment)-(PATINDEX('%Inspectionid:%',@UserComment)+13))
