Post
Ask Question

Get a Character, Numeric or Special Character String Value in SQL Server

Introduction

In this user-defined function, many system-predefined functions are used. This function returns table-type data because I wrote it as a table-valued function.

This type of function returns a table type of data. This function has two parameters, one is @value and the other is @condition. The @value parameter holds the string value like "asdf1234#^$&, asdf878*&^7" and the @condition parameter has the condition like "%[a-z0-9]%". We don't need the name of the table, this @tamptable is hidden. We need only the name of the column (filter).

Why do we need it?

set @value+=',' we need it because the loop skips the last value after the "," (comma).

This loop continues until @length is set to the value zero.

set @holdvalue=(left(@value,(CHARINDEX(',',@value)-1))); 

This line sets the @holdvalue to a single value like "12sdf#$%$". The charindex function returns the int type of value and the left function returns the string type of value. The charindex function returns the location of the "," split character. Look at this simple example:

select charindex(',','234asdf,asdf453');   

Output is: 8

The Left function splits the value on behalf of the location. For example:

select left('asdf@#$@#ew234',8);  

Output is: asdf@#$@

In the following the while loops until @holdvalue is not null. If the @holdvalue is null then the loop breaks automatically.

WHILE PatIndex(@condition, @holdvalue)

A Patindex function returns an Int type of value. For example.

select PatIndex('%[a-z0-9]%', 'as254#44$$#');

Output is: 1

The staff function is very useful in SQL Server. it returns a string type of value. Look at this example.

select Stuff('as254#44$$#', PatIndex('%[^a-zA-Z]%', 'as254#44$$#'), 1, '')   

Output is: 2

Let's verify that everything is correct.

  1. select * from [filterNumberWordSpecialCharacter]('as254#44$$#,45$%asdf','%[a-z0-9]%'); --Return a special Character only   
  2. select * from [filterNumberWordSpecialCharacter]('as254#44$$#,45$%asdf','%[^a-zA-Z]%'); --return only character only   
  3. select * from [filterNumberWordSpecialCharacter]('as254#44$$#,45$%asdf','%[^0-9]%'); --return a Number only   

Yahoo! It's working.

After you complete all the processes you will like this function with output.

output

I hope you enjoy this small article. Please feel free to reply to this article.


Similar Articles