Split strings often have delimiter
characters in their value(data/string value)
Tip
Use sam_fn_Alphabet_Split_In_String to
separate first letter of word from a string. If your input is
"This_Is_My_Example", split on the "_" to get
value of: "time".
Example
String Value - "This_Is_My_Function".
NOTE - "dbo" is your database schema
name.
Select
dbo.sam_fn_Alphabet_Split_In_String('This_Is_My_Example','_')
Output - "time".
To begin, let's examine the simplest
sam_fn_Alphabet_Split_In_String function in SQL Server. You already know the
general way to do this, but it is good to see
the basic syntax.
--===========================================================
--
CREATED BY : <SAMBHAV>
--
CREATED DATE : <--/--/---->
--
MODIFY BY : <SAMBHAV>
--
MODIFY DATE : <--/--/---->
--
DESCRIPTION : <Spilit Letter Of Word From String>
--
EXECUTE YOUR FUNCTION
--
Select dbo.sam_fn_Alphabet_Split_In_String('This_Is_My_Example','_')
--==========================================================
CREATE
FUNCTION sam_fn_Alphabet_Split_In_String(@String
VARCHAR(200),
@Delimiter CHAR(1))
RETURNS
VARCHAR(200)
AS
BEGIN
DECLARE
@sam VARCHAR(200)
SET
@sam=SUBSTRING(@string,1,1)
DECLARE
@idx int
DECLARE
@slice VARCHAR(200)
SELECT
@idx = 1
IF
LEN(@String)<1
OR @String IS
NULL
RETURN
LOWER(@sam)
WHILE
@idx!= 0
BEGIN
SET
@idx = CHARINDEX(@Delimiter,@String)
IF
@idx!=0
BEGIN
SET
@slice =
SUBSTRING(@string,@idx+1,1)
END
ELSE
BEGIN
SET
@slice =
SUBSTRING(@string,1,1)
END
IF(LEN(@slice)>0)
SET
@sam=@sam+@slice
SET
@String = RIGHT(@String,LEN(@String)
- @idx)
IF
LEN(@String)
= 0 BREAK
END
RETURN
LOWER(LEFT(@sam,LEN(@sam)-1)
)
END
--==========================================================
Summary
We used the
dbo.sam_fn_Alphabet_Split_In_String function. We separated alphabet letter
of word from a string and
solved problems. And it keeps your code as
simple as possible.