Step 1:- Before starting to know
this we have to learn one function the Sql server
PATINDEX()
Returns the starting
position of the first occurrence of a pattern in a specified expression, or
zeros if the pattern is not found, on all valid text and character data types
Example 1
Write the below sentences
SELECT PATINDEX('%lokeshasp%', 'Have You ever seen this lokeshasp.blogspot.com ');
--This is also
used for the first get the char index and the word index
OutPut:-
25
Example 2
DECLARE @GetIndex INT,@string varchar(100)
SET @string='welcome-to''lokeshasp.blogspot.com#$'
SET @GetIndex = PATINDEX('%[^a-zA-Z0-9 ]%', @string)
WHILE @GetIndex > 0
BEGIN
SET @string = STUFF(@string, @GetIndex, 1, ' ' )
SET @GetIndex = PATINDEX('%[^a-zA-Z0-9 ]%', @string)
END
SELECT @string
OutPut :-
welcome to
lokeshasp blogspot com