Solved

SQL Select

Posted on 2016-08-08
6
56 Views
Last Modified: 2016-08-15
Hello, in my table, I need to get text after DBA

for example, sometimes the string will be:   dba:Test123
sometimes :  dba: testing
sometimes:  DBA testing 1234

so in the strings, its not always the same, you have  " : "  sometimes not, sometimes just a space...

how can I do the select based on those rules ?  to sbstring only what is afer
0
Comment
Question by:PhilippeRenaud
6 Comments
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 250 total points
Comment Utility
SELECT SUBSTRING(string, CHARINDEX('DBA', string) + 3, 8000)

That will show the entire string if 'DBA' does not appear.
If instead you  want to show an empty string if 'DBA' does not appear in the original string, then do this:

SELECT SUBSTRING(string, CHARINDEX('DBA', string + 'DBA') + 3, 8000)
0
 
LVL 1

Author Comment

by:PhilippeRenaud
Comment Utility
it doest work... : if I do :

  SELECT SUBSTRING('DBA: Test', CHARINDEX('DBA', 'DBA: Test' + 'DBA') + 1, 8000)

Open in new window



it gives me :

BA: Test
0
 
LVL 29

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 250 total points
Comment Utility
Because you didn't add 3, as Scott suggested. With +1 you only skip the D of DBA. As you want to skip the three characters D, B, and also A, you have to add 3 to the index position CHARINDEX returns, as it returns the start position of the string searched, not the end position.

Bye, Olaf.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Sorry, I originally used "+1" and later corrected it to "+3" ... was hoping to get it updated before anyone saw the original :).
0
 
LVL 29

Expert Comment

by:Olaf Doschke
Comment Utility
That explains it. Nevertheless not impossible to figure out, once you look up what CHARINDEX means.

Bye, Olaf.
0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
Do you have DBA repeated in your column? If not, you can try like this.
declare @table table (string varchar(100))
insert @table values ('dba:Test123'),('DBA testing 1234')

select ltrim(replace(replace(string, 'dba:',''),'dba','')) from @table

-- Test123
-- testing 1234

Open in new window

0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now