• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 93
  • Last Modified:

SQL Select

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
2 Solutions
Scott PletcherSenior DBACommented:
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)
PhilippeRenaudAuthor Commented:
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
Olaf DoschkeSoftware DeveloperCommented:
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.
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Scott PletcherSenior DBACommented:
Sorry, I originally used "+1" and later corrected it to "+3" ... was hoping to get it updated before anyone saw the original :).
Olaf DoschkeSoftware DeveloperCommented:
That explains it. Nevertheless not impossible to figure out, once you look up what CHARINDEX means.

Bye, Olaf.
SharathData EngineerCommented:
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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now