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 (https://blog.sqlauthority.com)
12 Comments. Leave new
Added 2 more test cases:
SELECT dbo.GetFirstWord (NULL) AS FirstWordofSentence;
SELECT dbo.GetFirstWord (12345) AS FirstWordofSentence;
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
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.
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
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…
See if you can make use of this post
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.
First letter of the word can be extracted using left function. Ex
select left(‘testing’,1) ;
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
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)
Great. Thanks for sharing.