String Split - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

String Split

Author

Topic

WoodHouse
Posting Yak Master

211 Posts

Posted - 2012-06-11 : 12:53:07

Hi Guys,

I want to extract the square bracket letters from below column values.. please help me on it

r on [Change1 17] (slot number=16
r on [Change1 21] slot number=20
r for [Change2 2] on NetBackup 5
r for [Change1 14] on NetBackup 5
r for [Change1 7] on NetBackup 52
r on [Change1 3] slot number=2 fo
r on [Change1 1] for NetBackup 5
r on [Change3 24 Change2 2] f
r on [Change3 24 Change2 2] f
r on [Change3 41 Change2 1] fo
r on [Change3 24 Change2 1] fo
r on [Change3 41 Change1 14] f
r on [Change2 2] for NetBackup 5
r [Change2 2] on NetBackup 5200
r [Change2 2] on NetBackup 5020
r [Change1 12] on NetBackup 5020
r for [Change1 18 and Change1 9] on
r on [Change3 24 Change2 1] fo
r on [Change3 24 Change2 1] fo
r on [Change3 39 Change2 1] fo
r on [Change3 39 Change2 1] fo

For Example Expected output is

Change1 17
Change1 21
Change1 3
Change1 1
Change3 24 Change2 2
Change3 24 Change2 2
Change3 41 Change2 1
Change3 24 Change2 1
Change3 41 Change1 14
Change2 2
Change2 2
Change2 2
Change1 12

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-11 : 13:42:27

What is "bolted"? Do you mean bolded? If so, I don't see any bold text in your post. You'll want to use the b tags with square brackets around the data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-06-12 : 07:41:00

You can do the Split like this:


--Creating Table

Create Table Ex
(Column1 varchar(50) )


--Inserting Sample Data

Insert into Ex
Select 'r on [Change1 17] (slot number=16'
Union ALL
Select 'r on [Change1 21] slot number=20'
Union ALL
Select 'r for [Change2 2] on NetBackup 5'
Union ALL
Select 'r for [Change1 14] on NetBackup 5'
Union ALL
Select 'r for [Change1 7] on NetBackup 52'
Union ALL
Select 'r on [Change1 3] slot number=2 fo'
Union ALL
Select 'r on [Change1 1] for NetBackup 5'
Union ALL
Select 'r on [Change3 24 Change2 2] f'
Union ALL
Select 'r on [Change3 24 Change2 2] f'
Union ALL
Select 'r on [Change3 41 Change2 1] fo'
Union ALL
Select 'r on [Change3 24 Change2 1] fo'
Union ALL
Select 'r on [Change3 41 Change1 14] f'
Union ALL
Select 'r on [Change2 2] for NetBackup 5'
Union ALL
Select 'r [Change2 2] on NetBackup 5200' 
Union ALL
Select 'r [Change2 2] on NetBackup 5020' 
Union ALL
Select 'r [Change1 12] on NetBackup 5020'
Union ALL
Select 'r for [Change1 18 and Change1 9] on' 
Union ALL
Select 'r on [Change3 24 Change2 1] fo'
Union ALL
Select 'r on [Change3 24 Change2 1] fo'
Union ALL
Select 'r on [Change3 39 Change2 1] fo'
Union ALL
Select 'r on [Change3 39 Change2 1] fo'


--Query For Your Requirement

Select SUBSTRING(Column1, CHARINDEX('[', Column1) + 1, CHARINDEX(']', Column1) - 2) From
	(Select SUBSTRING(Column1, CHARINDEX('[', Column1), LEN(Column1)) As Column1 From Ex) As a

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources