How to get a substring between two characters?

  • 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

  • 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

  • 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)

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

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

  • 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]

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

  • Thanks guys for your assistance. Very helpful.

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

    regards

    Kevin

  • 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

  • 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/

  • .

  • 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

  • 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

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

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