We have a requirement like to split a text or string into single character into the table format. For example we have a text ‘Bagesh Kumar’ and we need output as below
|
id |
str_value |
|
1 |
B |
|
2 |
a |
|
3 |
g |
|
4 |
e |
|
5 |
s |
|
6 |
h |
|
7 |
|
|
8 |
k |
|
9 |
u |
|
10 |
m |
|
11 |
a |
|
12 |
r |
Below is
the script to get this result.
|
CREATE TABLE #temp ( id INT IDENTITY (1, 1) PRIMARY KEY, str_value VARCHAR(5) ) DECLARE @str AS VARCHAR(50); SET @str='Bagesh kumar singh 1234 &^%$#@ 980'; INSERT INTO #temp (str_value) SELECT Substring(@str, number, 1) FROM
master..spt_values WHERE ( Substring(@str, number, 1) LIKE '[A-Z]' OR Substring(@str, number, 1) LIKE '[0-9]' OR ( Substring(@str, number, 1) LIKE '[^0-9]' AND Substring(@str, number, 1) LIKE '[^A-Z]' ) ) AND type = 'P'; SELECT * FROM #temp; |
Running this
script we are getting the result as below.
Got the
expected result.
Read: Findletters, numbers and special characters from a column in sql server
https://bageshkumarbagi-msbi.blogspot.com/2016/11/find-letters-numbers-and-special.html
