Solved

Reverse searching text string in SQL

Posted on 2016-08-31
6
41 Views
Last Modified: 2016-09-16
Hello Experts,

I'm after some code to use in SQL Server which allows to pick up 2 different user id's stored within a text string.

I have code which will let me pick up the first ID moving left to right but i'd like to pick up the 2nd id moving from right to left.

The text string will be something like 'Name D239383 Name D938373'

I'm using the below code to get the first UID D239383:
Case When string Like '%D[0-9]%' then 'D'+SUBSTRING(string,PATINDEX('%D[0-9]%',string)+1,6) ELSE Null END

replacing 'string' with the proper field name.

I tried using the REVERSE command but couldn't seem to get it to work.

And assistance would be greatly appreciated!
0
Comment
Question by:Chris_Sizer
  • 2
  • 2
  • 2
6 Comments
 
LVL 51

Expert Comment

by:Ryan Chong
ID: 41777692
quick try:
declare @string nVarchar(100)
set @string =  'Name D239383 Name D938373'

select @string, parsename(replace(@string, 'Name', '.'), 1)

Open in new window

0
 

Author Comment

by:Chris_Sizer
ID: 41777712
Hi Ryan,

Thanks for the reply!
If i understand this correctly, you are looking to replaced the word Name with a '.'?

If that's the case i'm not sure this will work as a team members name will be different in each instnace.

Also, how would i bring through the column name in your example?
0
 
LVL 51

Expert Comment

by:Ryan Chong
ID: 41777762
>>If i understand this correctly, you are looking to replaced the word Name with a '.'?
yes, that's the idea when we are using function parsename.

>>If that's the case i'm not sure this will work as a team members name will be different in each instnace.
do you have other instances can show here?
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 41777766
Hi,

pls try

Case When string Like '%D[0-9]%D[0-9]%' then 'D'+SUBSTRING(SUBSTRING(string,PATINDEX('%D[0-9]%',string)+1,LEN(string)),PATINDEX('%D[0-9]%',SUBSTRING(string,PATINDEX('%D[0-9]%',string)+1,Len(string))+1,6) ELSE  Null END

Open in new window

Regards
0
 

Author Comment

by:Chris_Sizer
ID: 41777808
Hi Rgonzo1971,

This is working well, but it seems to be bringing through part of the 2nd name as well as the remainder of the text string.

Any ideas?
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 41777838
then try
select Case When string Like '%D[0-9]%D[0-9]%' then 'D'+SUBSTRING(SUBSTRING(string,PATINDEX('%D[0-9]%',string)+1,LEN(string)),PATINDEX('%D[0-9]%',SUBSTRING(string,PATINDEX('%D[0-9]%',string)+1,Len(string)))+1,6) ELSE  Null END

Open in new window

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Help  needed 3 37
SQL Improvement  ( Speed) 14 33
tempdb log keep growing 7 35
Amazon RDS migrate to SQL Server 3 26
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

821 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