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

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 (http://blog.sqlauthority.com)

10 thoughts on “SQL SERVER – Function to Retrieve First Word of Sentence – String Operation

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

  7. Pingback: SQL SERVER – Weekly Series – Memory Lane – #043 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s