How to get a substring between two characters?

Question

How to get a substring between two characters?

ayfour

Right there with Babe

Points: 734
More actions
August 16, 2010 at 4:13 am

#225840

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

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

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

aravind_0404 Say Hey Kid Points: 680 More actions · Answer 2

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)

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

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..

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 4

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!! 🙂

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 5

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...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 6

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 🙂

ayfour Right there with Babe Points: 734 More actions · Answer 7

Thanks guys for your assistance. Very helpful.

I will get to work today, and put your suggestions into action.

regards

Kevin

kmole SSC Veteran Points: 223 More actions · Answer 8

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

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/

kmole SSC Veteran Points: 223 More actions · Answer 10

kmole

SSC Veteran

Points: 223

April 9, 2015 at 3:35 pm

#1790036

.

kmole SSC Veteran Points: 223 More actions · Answer 11

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 12

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)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2