August 16, 2010 at 4:13 am
Hi
Can anyone help me with an easy way to extract a substring that is between 2 characters?
Example string: 123n_abcn_123n
The substrings before and after the underscores can be any length.
My objective is to be able to extract the "abcn" from a column of values.
Thank you for your assistance.
regards
Kevin
August 16, 2010 at 4:23 am
Kevin
This sort of string manipulation is best performed by your application rather than SQL Server. If you really need to do it, you should be able to use the LEFT, RIGHT, SUBSTRING and REVERSE functions to trim off the beginning and end of the strings, leaving only the bit in the middle that you need. Have a go at that, and post back if you have any specific problems or questions.
John
August 16, 2010 at 4:27 am
Hope this helps:
SELECT SUBSTRING('123n_abcn_123n',CHARINDEX('_','123n_abcn_123n')+1, CHARINDEX('_','123n_abcn_123n',CHARINDEX('_','123n_abcn_123n')+1)-CHARINDEX('_','123n_abcn_123n')-1)
August 16, 2010 at 6:06 am
WOW.... THIS IS MY 1000th POST... HAPPPY ME!! :w00t: :cool::cool:
Take this brother:
IF OBJECT_ID (N'dbo.udf_GetStringBetween2Chars', N'FN') IS NOT NULL
BEGIN
PRINT 'FUNCTION : dbo.udf_GetStringBetween2Chars ALREADY PRESENT, SO DROPPING IT..'
DROP FUNCTION dbo.udf_GetStringBetween2Chars;
PRINT 'CREATING FUNCTION : dbo.udf_GetStringBetween2Chars'
END
ELSE
BEGIN
PRINT 'FUNCTION : dbo.udf_GetStringBetween2Chars IS NOT PRESENT'
PRINT 'CREATING FUNCTION : dbo.udf_GetStringBetween2Chars'
END
GO
CREATE FUNCTION dbo.udf_GetStringBetween2Chars (@String VARCHAR(50) , @SpecialChar CHAR(1))
RETURNS VARCHAR(50)
AS
BEGIN
/*Declaring Local Variables*/
DECLARE @FirstIndexOfChar INT,
@LastIndexOfChar INT,
@LengthOfStringBetweenChars INT
SET @FirstIndexOfChar= CHARINDEX(@SpecialChar,@String,0)
SET @LastIndexOfChar= CHARINDEX(@SpecialChar,@String,@FirstIndexOfChar+1)
SET @LengthOfStringBetweenChars = @LastIndexOfChar - @FirstIndexOfChar -1
SET @String = SUBSTRING(@String,@FirstIndexOfChar+1,@LengthOfStringBetweenChars)
RETURN @String
END
GO
Happy to have helped and learnt a lottttt... wow..
August 16, 2010 at 6:28 am
Or even betters solution:
Functions Script:
IF OBJECT_ID (N'dbo.iTVF_GetStringBetween2Chars', N'IF') IS NOT NULL
BEGIN
PRINT 'FUNCTION : dbo.iTVF_GetStringBetween2Chars ALREADY PRESENT, SO DROPPING IT..'
DROP FUNCTION dbo.iTVF_GetStringBetween2Chars;
PRINT 'CREATING FUNCTION : dbo.iTVF_GetStringBetween2Chars'
END
ELSE
BEGIN
PRINT 'FUNCTION : dbo.iTVF_GetStringBetween2Chars IS NOT PRESENT'
PRINT 'CREATING FUNCTION : dbo.iTVF_GetStringBetween2Chars'
END
GO
CREATE FUNCTION dbo.iTVF_GetStringBetween2Chars (@String VARCHAR(50) , @SpecialChar CHAR(1))
RETURNS TABLE
AS
RETURN
(
SELECT
SUBSTRING(@String, FirstIndexOfChar +1 , (LastIndexOfChar-FirstIndexOfChar-1) ) AS String
FROM
(
SELECT
FirstIndexOfChar,
CHARINDEX(@SpecialChar,@String,FirstIndexOfChar+1) LastIndexOfChar
FROM
(
SELECT CHARINDEX(@SpecialChar,@String,0) AS FirstIndexOfChar
) AS [First]
) AS [Last]
)
GO
Usage Example:
declare @table table
(string varchar(50))
insert into @table
select 'aaaaa_bbbbb_cccc' union all
select 'xx_yyyyyyyy_zz' union all
select 'mmmmm_n_oo'
select iTVF.String cut
FROM @table Tab
CROSS APPLY
iTVF_GetStringBetween2Chars (Tab.string,'_') iTVF
Hope this helps!! 🙂
August 16, 2010 at 6:40 am
Personally, I wouldn't use UDF for the requested task, even it's look neater, it will be slower than just doing it in-line...
However, if you will go into using UDF, remember to add WITH SCHEMABINDING option into UDF declaration. It will help SQL Server to see this function as Deterministic, which will potentially lead to much better performance when it's used...
August 16, 2010 at 6:58 am
Eugene Elutin (8/16/2010)
Personally, I wouldn't use UDF for the requested task, even it's look neater, it will be slower than just doing it in-line....
Eug, I knew you guys will hate UDF's; that why i coded an iTVF and have given the code in my last post of this thread 🙂
August 16, 2010 at 4:17 pm
Thanks guys for your assistance. Very helpful.
I will get to work today, and put your suggestions into action.
regards
Kevin
April 9, 2015 at 3:23 pm
Hi John,
I thought I had used what you saidby trimming the front and back off which works fine when a middle name is present.
BUT when it is only a first and last name it keeps returning the last name where I'd like it to be null
Can you please check out my code and see where I am going wrong!
Thanks
CREATE FUNCTION udf_get_middle_name (@string NVARCHAR (100))
RETURNS NVARCHAR(50)
AS
BEGIN
IF (dbo.UDF_get_number_of_spaces (@string) >1)
BEGIN
SET @string = REPLACE (@string, LEFT(@string, CHARINDEX (' ', @string)),'')
SET @string = LTRIM (@string)
SET @string = RTRIM (@string)
SET @string = REVERSE (@string)
SET @string = REPLACE (@string, LEFT(@string, CHARINDEX (' ', @string)),'')
SET @string = LTRIM (@string)
SET @string = RTRIM (@string)
SET @string = REVERSE (@string)
RETURN @string
END
ELSE
SET @string = NULL
RETURN @string
END
April 9, 2015 at 3:29 pm
kyliebjoe (4/9/2015)
Hi John,I thought I had used what you saidby trimming the front and back off which works fine when a middle name is present.
BUT when it is only a first and last name it keeps returning the last name where I'd like it to be null
Can you please check out my code and see where I am going wrong!
Thanks
CREATE FUNCTION udf_get_middle_name (@string NVARCHAR (100))
RETURNS NVARCHAR(50)
AS
BEGIN
IF (dbo.UDF_get_number_of_spaces (@string) >1)
BEGIN
SET @string = REPLACE (@string, LEFT(@string, CHARINDEX (' ', @string)),'')
SET @string = LTRIM (@string)
SET @string = RTRIM (@string)
SET @string = REVERSE (@string)
SET @string = REPLACE (@string, LEFT(@string, CHARINDEX (' ', @string)),'')
SET @string = LTRIM (@string)
SET @string = RTRIM (@string)
SET @string = REVERSE (@string)
RETURN @string
END
ELSE
SET @string = NULL
RETURN @string
END
You really should start a new thread. This one is 5 years old.
The first place you went wrong is in creating a scalar UDF. From a performance perspective that is about as slow as you can get. If you can post clearly what you are trying to do we can help you find a way to do this efficiently in a single select statement.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 9, 2015 at 3:35 pm
.
April 9, 2015 at 3:42 pm
I need to use a UDF
this function calls on another function that counts the number of spaces in the persons name.
If the name has two spaces, I want to be able to return the text between the two spaces. (which works fine when a middle name is present)
If the count is less than 2 spaces then I would like it to return NULL. BUT at them moment if no middle name is present it is putting the last name into the middle name column
April 9, 2015 at 3:48 pm
Calling a scalar UDF inside another scalar UDF is a call for disaster.
Sean mentioned that this could be done in a single statement because inline table-valued functions are pretty fast. Here's an article explaining how to do it. http://www.sqlservercentral.com/articles/T-SQL/91724/
And since I just had the code for a very similar problem, here's an example on how you could do it.
CREATE FUNCTION tudf_get_middle_name (@string NVARCHAR (100))
RETURNS TABLE
AS
RETURN
--DECLARE @string NVARCHAR (100) = 'Luis A Cazares'
SELECT CASE
WHEN LEN( @string) - LEN(REPLACE(@String, ' ', '')) > 1 --This will give number of spaces
THEN LEFT( Middle.name, CHARINDEX( ' ', Middle.name) - 1)
END
FROM (SELECT STUFF( @string, 1, CHARINDEX( ' ', @string), ''))Middle(name)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply