SQL SERVER – Function to Retrieve First Word of Sentence – String Operation

I have sent of function library where I store all the UDF I have ever written. Recently I received email from my friend requesting if I have UDF which manipulate string and returns only very first word of the statement. Well, I realize that I do not have such a script at all. I found myself writing down this similar script after long time. Let me know if you know any other better script to do the same task.

DECLARE @StringVar VARCHAR(100)
SET @StringVar = ' anything '
SELECT CASE CHARINDEX(' ', LTRIM(@StringVar), 1)
WHEN 0 THEN LTRIM(@StringVar)
ELSE SUBSTRING(LTRIM(@StringVar), 1, CHARINDEX(' ',LTRIM(@StringVar), 1) - 1)
END FirstWordofSentence


Solarwinds

Above script can be converted to User Defined Function which you can later use in SELECT statement as well. Here is how you can convert it to UDF and use it afterwords.

CREATE FUNCTION GetFirstWord (@StringVar VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @rtStringVar VARCHAR(MAX)
SELECT @rtStringVar = CASE CHARINDEX(' ', LTRIM(@StringVar), 1)
WHEN 0 THEN LTRIM(@StringVar)
ELSE SUBSTRING(LTRIM(@StringVar), 1, CHARINDEX(' ',LTRIM(@StringVar), 1) - 1)
END
RETURN @rtStringVar
END
GO
--Usage of Function
SELECT dbo.GetFirstWord ('') AS FirstWordofSentence;
SELECT dbo.GetFirstWord (' Space Before') AS FirstWordofSentence;
SELECT dbo.GetFirstWord ('Long Statement') AS FirstWordofSentence;
SELECT dbo.GetFirstWord ('SingleWord') AS FirstWordofSentence;

You can see in the resultset where it will find the very first word of the any sentence.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQL SERVER – Negative Identity Seed Value and Negative Increment Interval
Next Post
SQL SERVER – Computed Column – PERSISTED and Storage – Part 2

Related Posts

12 Comments. Leave new

  • Thanks Pinal Sir,

    I really like Your post on all the topics.

    Always keen to find out everything on u r blog now its first search option for me for SqlServer.

    Now https://blog.sqlauthority.com/ Is my home page for Browsing I don’t want any blog to miss u post..

    Thanks
    SUHAS

    Reply
  • Added 2 more test cases:

    SELECT dbo.GetFirstWord (NULL) AS FirstWordofSentence;
    SELECT dbo.GetFirstWord (12345) AS FirstWordofSentence;

    Reply
  • Hi Pinal,

    Here’s one you might find interesting:

    Regards,

    Greg

    CREATE FUNCTION GetFirstWord (@StringVar VARCHAR(MAX))
    RETURNS VARCHAR(MAX)
    AS
    BEGIN
    RETURN (SELECT TOP(1) display_term
    FROM sys.dm_fts_parser(‘”‘ + @StringVar + ‘”‘,1033,NULL,0));
    END;
    GO

    Reply
  • If you’re searching large strings, it is often significantly quicker to convert to binary first, e.g.:

    — looks for an occurrence of a space (ascii 32, hex 0x20)
    CHARINDEX(0x20, CAST(LTRIM(@StringVar) AS VARBINARY(100)), 1)

    Over a lot of rows, you’ll notice the difference in performance.

    Reply
  • Maybe a slightly lighter weight option is:

    create function GetFirstWordAlt — mjfii
    (
    @i_String nvarchar(max)
    )
    returns nvarchar(max)
    as
    begin
    return ltrim(left(@i_String,patindex(‘% %’,ltrim(@i_String+’ ‘))))
    end
    go

    Adding a space to the input string and using patindex with wildcards allows you to ignore any leading space and grab entire words…

    mjfii

    Reply
  • Hi Pinal,

    How to extract whole sentence from a large paragraph? is there any solution to extract sentence if any word is matching in large string? I want to extract a sentence from a paragraph when any word is available in my searching string.
    e.g

    my name is solanki jayant. I am sql server programmer. i like to write complex query.

    Search string : solanki

    as per above example solanki is available in first sentence then i need to get only single sentence from a large pragraph.

    Please help…

    Reply
  • Hi,

    I have got into a discussion about the use of ISNULL and SUBSTRING in SQL Query.
    There is a ‘INSERT INTO SELECT * ‘ statement in a stored procedure.
    Requirement is to insert the first letter of the word and insert into a table.

    Reply
    • First letter of the word can be extracted using left function. Ex

      select left(‘testing’,1) ;

      Reply
  • Hi,

    I have got into a discussion about the use of ISNULL and SUBSTRING in SQL Query.

    There is a ‘INSERT INTO SELECT * ‘ statement in a stored procedure.
    Requirement is to insert the first letter of the word and insert into a table.I used SUBSTRING fn.If the field in select statement is empty insert some default value. I used ISNULL for this. Below is the statement:

    INSERT INTO t_al_host_po_master (
    host_group_id, record_create_date, processing_code, wh_id, client_code, po_number,display_po_number, po_type, vendor_code, status, residential_flag, ship_from_name,ship_from_addr1, ship_from_addr2, ship_from_city, ship_from_state, ship_from_postal_code, ship_from_country_code, ship_from_attention, ship_from_phone, ship_from_fax, carrier_scac,carrier_mode, service_level, freight_terms
    )
    SELECT @v_vchHostGroupID, @v_dtRecordCreateDate, tb2.TransactionCode, tb1.WarehouseID,tb1.ClientCode, tb1.InboundOrderNumber, tb1.DisplayIONumber, tb2.IOType, tb2.VendorCode,

    ISNULL(SUBSTRING(tb2.Status,1,1),’O’), SUBSTRING(ISNULL(tb2.ResidentialFlag, ‘N’),1,1),

    tb2.ShipFromName, tb2.ShipFromAddr1, tb2.ShipFromAddr2,tb2.ShipFromCity, tb2.ShipFromState, tb2.ShipFromPostalCode, tb2.ShipFromCountryCode, tb2.ShipFromAttention, tb2.ShipFromPhone, tb2.ShipFromFax, tb2.CarrierSCAC, tb2.CarrierMode, tb2.ServiceLevel, tb2.FreightTerms FROM t_xml_imp_io_master tb1 WITH (NOLOCK) INNER JOIN t_xml_imp_io_master_info tb2 WITH (NOLOCK)
    ON tb2.hjs_parent_id = tb1.hjs_node_id

    Please look at the use of ISNULL and SUBSTRING. These two can be combined in two ways as is there in query.Which is better to use ISNULL(SUBSTRING()) or SUBSTRING(ISNULL()).
    Both return same result.

    Thanks,
    Vishy

    Reply
  • Thanks Dave. Needed something similar and developed a more generic GetNthWord

    CREATE FUNCTION GetNthWord (@StringVar VARCHAR(MAX), @WordNum int)
    RETURNS VARCHAR(MAX)
    AS
    BEGIN
    DECLARE @rtStringVar VARCHAR(MAX)
    if CHARINDEX(‘ ‘, LTRIM(@StringVar), 1) = 0
    return ”;
    else
    begin
    set @rtStringVar=@StringVar
    while @WordNum>1
    begin
    set @rtStringVar= SUBSTRING(LTRIM(@rtStringVar), CHARINDEX(‘ ‘,LTRIM(@rtStringVar), 1), Len(@rtStringVar))
    set @WordNum = @WordNum -1
    if CHARINDEX(‘ ‘, LTRIM(@rtStringVar), 1) = 0 And @WordNum>1
    set @rtStringVar = ‘ ‘;
    end
    end
    if CHARINDEX(‘ ‘, LTRIM(@rtStringVar), 1) = 0
    return @rtStringVar;
    RETURN SUBSTRING(LTRIM(@rtStringVar), 1, CHARINDEX(‘ ‘,LTRIM(@rtStringVar), 1) – 1)
    END
    GO

    SELECT dbo.GetNthWord(‘Sgt. Pepper and the band’, -1)
    SELECT dbo.GetNthWord(‘Sgt. Pepper and the band’, 0)
    SELECT dbo.GetNthWord(‘Sgt. Pepper and the band’, 1)
    SELECT dbo.GetNthWord(‘Sgt. Pepper and the band’, 2)
    SELECT dbo.GetNthWord(‘Sgt. Pepper and the band’, 3)
    SELECT dbo.GetNthWord(‘Sgt. Pepper and the band’, 4)
    SELECT dbo.GetNthWord(‘Sgt. Pepper and the band’, 5)
    SELECT dbo.GetNthWord(‘Sgt. Pepper and the band’, 6)

    Reply

Leave a Reply

Menu