Get everything after and before certain character in SQL Server
SqlSql ServerSql Problem Overview
I got the following entry in my database:
images/test.jpg
I want to trim the entry so I get: test
So basically, I want everything after /
and before .
How can I solve it?
Sql Solutions
Solution 1 - Sql
use the following function
left(@test, charindex('/', @test) - 1)
Solution 2 - Sql
If you want to get this out of your table using SQL, take a look at the following functions that will help you: SUBSTRING
and CHARINDEX
. You can use those to trim your entries.
A possible query will look like this (where col
is the name of the column that contains your image directories:
SELECT SUBSTRING(col, LEN(SUBSTRING(col, 0, LEN(col) - CHARINDEX ('/', col))) + 1,
LEN(col) - LEN(SUBSTRING(col, 0, LEN(col) - CHARINDEX ('/', col))) - LEN(SUBSTRING(
col, CHARINDEX ('.', col), LEN(col))));
Bit of an ugly beast. It also depends on the standard format of 'dir/name.ext'.
Edit:
This one (inspired by praveen) is more generic and deals with extensions of different length:
SELECT SUBSTRING(col, LEN(LEFT(col, CHARINDEX ('/', col))) + 1, LEN(col) - LEN(LEFT(col,
CHARINDEX ('/', col))) - LEN(RIGHT(col, LEN(col) - CHARINDEX ('.', col))) - 1);
Solution 3 - Sql
Before
SELECT SUBSTRING(ParentBGBU,0,CHARINDEX('/',ParentBGBU,0)) FROM dbo.tblHCMMaster;
After
SELECT SUBSTRING(ParentBGBU,CHARINDEX('-',ParentBGBU)+1,LEN(ParentBGBU)) FROM dbo.tblHCMMaster
Solution 4 - Sql
----select characters before / including /
select SUBSTRING ('abcde/wxyz',0,CHARINDEX('/','abcde/wxyz')+1)
--select characters after / including /
select SUBSTRING('abcde/wxyz',CHARINDEX('/','abcde/wxyz'),LEN('abcde/wxyz'))
Solution 5 - Sql
declare @T table
(
Col varchar(20)
)
insert into @T
Select 'images/test1.jpg'
union all
Select 'images/test2.png'
union all
Select 'images/test3.jpg'
union all
Select 'images/test4.jpeg'
union all
Select 'images/test5.jpeg'
Select substring( LEFT(Col,charindex('.',Col)-1),charindex('/',Col)+1,len(LEFT(Col,charindex('.',Col)-1))-1 )
from @T
Solution 6 - Sql
I just did this in one of my reports and it was very simple.
Try this:
=MID(Fields!.Value,8,4)
Note: This worked for me because the value I was trying to get was a constant not sure it what you are trying to get is a constant as well.
Solution 7 - Sql
I have made a method which is much more general :
so :
DECLARE @a NVARCHAR(MAX)='images/test.jpg';
--Touch here
DECLARE @keysValueToSearch NVARCHAR(4000) = '/'
DECLARE @untilThisCharAppears NVARCHAR(4000) = '.'
DECLARE @keysValueToSearchPattern NVARCHAR(4000) = '%' + @keysValueToSearch + '%'
--Nothing to touch here
SELECT SUBSTRING(
@a,
PATINDEX(@keysValueToSearchPattern, @a) + LEN(@keysValueToSearch),
CHARINDEX(
@untilThisCharAppears,
@a,
PATINDEX(@keysValueToSearchPattern, @a) + LEN(@keysValueToSearch)
) -(PATINDEX(@keysValueToSearchPattern, @a) + LEN(@keysValueToSearch))
)
Solution 8 - Sql
I know this has been a while.. but here is an idea
declare @test varchar(25) = 'images/test.jpg'
select
@test as column_name
, parsename(replace(@test,'/','.'),1) as jpg
,parsename(replace(@test,'/','.'),2) as test
,parsename(replace(@test,'/','.'),3) as images
Solution 9 - Sql
SELECT Substring('[email protected]', 1, ( Charindex('@', '[email protected]')
- 1 ))
Before,
RIGHT('[email protected]', ( Charindex('@', '[email protected]') + 1 ))
After
Solution 10 - Sql
Below query gives you data before '-' Ex- W12345A-4S
SELECT SUBSTRING(Column_Name,0, CHARINDEX('-',Column_Name)) as 'new_name'
from [abc].
Output - W12345A
Solution 11 - Sql
I found Royi Namir's answer useful but expanded upon it to create it as a function. I renamed the variables to what made sense to me but you can translate them back easily enough, if desired.
Also, the code in Royi's answer already handled the case where the character being searched from does not exist (it starts from the beginning of the string), but I wanted to also handle cases where the character that is being searched to does not exist.
In that case it acts in a similar manner by starting from the searched from character and returning the rest of the characters to the end of the string.
CREATE FUNCTION [dbo].[getValueBetweenTwoStrings](@inputString
NVARCHAR(4000), @stringToSearchFrom NVARCHAR(4000), @stringToSearchTo
NVARCHAR(4000))
RETURNS NVARCHAR(4000)
AS
BEGIN
DECLARE @retVal NVARCHAR(4000)
DECLARE @stringToSearchFromSearchPattern NVARCHAR(4000) = '%' +
@stringToSearchFrom + '%'
SELECT @retVal = SUBSTRING (
@inputString,
PATINDEX(@stringToSearchFromSearchPattern, @inputString) + LEN(@stringToSearchFrom),
(CASE
CHARINDEX(
@stringToSearchTo,
@inputString,
PATINDEX(@stringToSearchFromSearchPattern, @inputString) + LEN(@stringToSearchFrom))
WHEN
0
THEN
LEN(@inputString) + 1
ELSE
CHARINDEX(
@stringToSearchTo,
@inputString,
PATINDEX(@stringToSearchFromSearchPattern, @inputString) + LEN(@stringToSearchFrom))
END) - (PATINDEX(@stringToSearchFromSearchPattern, @inputString) + LEN(@stringToSearchFrom))
)
RETURN @retVal
END
Usage:
SELECT dbo.getValueBetweenTwoStrings('images/test.jpg','/','.') AS MyResult
Solution 12 - Sql
You can try this:
Declare @test varchar(100)='images/test.jpg'
Select REPLACE(RIGHT(@test,charindex('/',reverse(@test))-1),'.jpg','')
Solution 13 - Sql
Inspired by the work of Josien, I wondered about a simplification.
Would this also work? Much shorter:
SELECT SUBSTRING(col, CHARINDEX ('/', col) + 1, CHARINDEX ('.', col) - CHARINDEX ('/', col) - 1);
(I can't test right now because of right issues at my company SQL server, which is a problem in its own right)
Solution 14 - Sql
> Simply Try With LEFT ,RIGHT ,CHARINDEX
select
LEFT((RIGHT(a.name,((CHARINDEX('/', name))+1))),((CHARINDEX('.', (RIGHT(a.name,
((CHARINDEX('/', name))+1)))))-1)) splitstring,
a.name
from
(select 'images/test.jpg' as name)a
Solution 15 - Sql
I got some invalid length errors. So i made this function, this should not give any length problems. Also when you do not find the searched text it will return a NULL.
CREATE FUNCTION [FN].[SearchTextGetBetweenStartAndStop](@string varchar(max),@SearchStringToStart varchar(max),@SearchStringToStop varchar(max))
RETURNS varchar(max)
BEGIN
SET @string = CASE
WHEN CHARINDEX(@SearchStringToStart,@string) = 0
OR CHARINDEX(@SearchStringToStop,RIGHT(@string,LEN(@string) - CHARINDEX(@SearchStringToStart,@string) + 1 - LEN(@SearchStringToStart))) = 0
THEN NULL
ELSE SUBSTRING(@string
,CHARINDEX(@SearchStringToStart,@string) + LEN(@SearchStringToStart) + 1
,(CHARINDEX(@SearchStringToStop,RIGHT(@string,LEN(@string) - CHARINDEX(@SearchStringToStart,@string) + 1 - LEN(@SearchStringToStart)))-2)
)
END
RETURN @string
END
Solution 16 - Sql
if Input= pg102a-wlc01s.png.intel.com and Output should be pg102a-wlc01s
we can use below query :
select Substring(pc.name,0,charindex('.',pc.name,0)),pc.name from tbl_name pc
Solution 17 - Sql
declare @searchStart nvarchar(100) = 'search ';
declare @searchEnd nvarchar(100) = ' ';
declare @string nvarchar(4000) = 'This is a string to search (hello) in this text ';
declare @startIndex int = CHARINDEX(@searchStart, @string,0) + LEN(@searchStart);
declare @endIndex int = CHARINDEX(@searchEnd, @string, @startIndex + 1);
declare @length int = @endIndex - @startIndex;
declare @sub nvarchar(4000) = SUBSTRING(@string, @startIndex, @length)
select @startIndex, @endIndex, @length, @sub
This is a little more legible than the one-liners in this answer which specifically answer the question, but not in a generic way that would benefit all readers. This could easily be made into a function as well with a slight modification.
Solution 18 - Sql
If there are more than one or none occurences of given character use this:
DECLARE @rightidx int = CASE
WHEN 'images/images/test.jpg' IS NULL OR (CHARINDEX('.', 'images/images/test.jpg')) <= 0 THEN LEN('images/images/test.jpg')
ELSE (CHARINDEX('.', REVERSE('images/images/test.jpg')) - 1)
END
SELECT RIGHT('images/images/test.jpg', @rightidx)
Solution 19 - Sql
This was the approach I took.
CREATE FUNCTION dbo.get_text_before_char(@my_string nvarchar(255),@my_char char(1))
RETURNS nvarchar(255)
AS
BEGIN;
return IIF(@my_string LIKE '%' + @my_char + '%',left (@my_string, IIF(charindex(@my_char, @my_string) - 1<1,1,charindex(@my_char, @my_string) - 1)),'');
END;
CREATE FUNCTION dbo.get_text_after_char(@my_string nvarchar(255),@my_char char(1))
RETURNS nvarchar(255)
AS
BEGIN;
return IIF ( @my_string LIKE '%' + @my_char + '%' ,RIGHT ( @my_string , IIF ( charindex ( @my_char ,reverse(@my_string) )-1 < 1 ,1 ,charindex ( @my_char ,reverse(@my_string) )-1 ) ) , '' )
END;
SELECT
dbo.get_text_before_char('foo-bar','-')
, dbo.get_text_after_char('foo-bar','-')
Solution 20 - Sql
declare @test varchar(100)='images/test.jpg'
select right(left(@test, charindex('.', @test) - 1),4)