I need to extract a part of substring from string which follows as per below.
YY_12.Yellow
ABC_WSA.Thisone_A
SS_4MON.DHHE_A_A
I need to extract the string as per below
Yellow
Thisone
DHHE
Add a comment
|
4 Answers
You could use something like this:
declare @tbl table (col nvarchar(100));
insert @tbl values ('YY_12.Yellow'), ('ABC_WSA.Thisone_A'), ('SS_4MON.DHHE_A_A')
select *
, charindex('_', col2, 0)
, left(col2,
case
when charindex('_', col2, 0) - 1 > 0
then charindex('_', col2, 0) - 1
else len(col2)
end) [result]
from (
select col
, substring(col, charindex('.', col, 0) + 1, len(col)) [col2]
from @tbl ) rs
I'm going to leave the full code so as you can hopefully understand what I did.
- First identify and remove everything up to the dot "." (in the
[col2]column in the nestedSELECT) - Then I nest that
SELECTso I can apply a new logic much easier on the result column from the firstSELECTfrom which I only keep everything up to the underscore "_" - The final result is stored in the
[result]column
Comments
Try this:
CREATE TABLE app (info varchar(20))
INSERT INTO app VALUES
('YY_12.Yellow'),
('ABC_WSA.Thisone_A'),
('SS_4MON.DHHE_A_A'),
('testnopoint')
SELECT
CASE
WHEN CHARINDEX('.', info) > 0 THEN
CASE
WHEN CHARINDEX('_', info, CHARINDEX('.', info) + 1) > 0 THEN
SUBSTRING(info, CHARINDEX('.', info) + 1, CHARINDEX('_', info, CHARINDEX('.', info) + 1) - CHARINDEX('.', info) - 1)
ELSE
SUBSTRING(info, CHARINDEX('.', info) + 1, LEN(info))
END
END
FROM app
My query, if . is not present returns NULL, if you want returns all string remove the CASE statement
Go on SqlFiddle
Comments
You could also try with parsename() function available from SQL Server 2012
select Name, left(parsename(Name,1),
case when charindex('_', parsename(Name,1)) > 0
then charindex('_', parsename(Name,1))-1
else len(parsename(Name,1))
end) [ExtrectedName] from table
This assumes you have always . in your string to read the name after .
Result :
Name ExtrectedName
YY_12.Yellow Yellow
ABC_WSA.Thisone_A Thisone
SS_4MON.DHHE_A_A DHHE
Comments
Try this, used STUFF here
SELECT LEFT(STUFF(col,1,CHARINDEX('.',col),''),
CHARINDEX('_',STUFF(col,1,CHARINDEX('.',col),'')+'_')-1
)
FROM @table
Output:-
Yellow
Thisone
DHHE
