I probably should create some group of articles for string manuipulation.At this time I'd like to show you an example to extract portion of text prior/after to some charcter/symbol.An idea is simple to iterate through string and return the text prior (in my example) to 'N' appearance of specified symbol.
CREATE FUNCTION dbo.udf_Extract(
@target varchar(8000),
@string varchar(8000),
@i int
) RETURNS varchar(8000) AS BEGIN
DECLARE @pos int
SET @pos = 0
WHILE @i > 0 BEGIN
SET @pos = CHARINDEX(@target,@string,@pos+1)
SET @i = @i - 1
IF @pos = 0 RETURN '0'
END
RETURN SUBSTRING (@string,1,@pos-1)
END
GO
--Usage
SELECT dbo.udf_Extract('@','hjdhjdhj@hjjsd@hjdfhdfj@jjj,3)
Subscribe to:
Post Comments (Atom)
3 comments:
Hi Uri Dimant
Will this give the same result of what your function gives?
declare @v varchar(100)
set @v='hjdhjdhj@hjjsd@hjdfhdfj@jjj'
select left(@v,len(@v)-charindex('@',reverse(@v)))
Well. I didnt notice the third parameter. If the string has maximum three @s then you can play with parsename function and display accordingly
declare @v varchar(100)
set @v='hjdhjdhj@hjjsd@hjdfhdfj@jjj'
set @v=replace(@v,'@','.')
select
parsename(@v,4),parsename(@v,3),parsename(@v,2),parsename(@v,1)
Post a Comment