Solved

How to replace the specific pattern in the string.

Posted on 2009-07-14
11
797 Views
Last Modified: 2012-05-07
I have a column in the table of type varchar and usually the column contains the values like :

389238473
387983483/A
74239384
291034734
8034834/A2
238905934/A1
609834
4638340738475/A5
001298434

In the above values, I want to remove/replace the values which end with '/A1', '/A2' ........  '/A9'  

i.e   238905934/A1   -->  238905934

The number followed by '/A' can be between 1 to 9.  Can I do this with replace function using pattern matching.

0
Comment
Question by:nazeemunnisa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
11 Comments
 
LVL 17

Expert Comment

by:pssandhu
ID: 24850812
UPDATE MyTable
SET Column = SUBSTRING(Column,1,CHARINDEX('/',Column)-1)
This will update all the value in your table by removing the / and the values after it. Please test first.
P.
0
 
LVL 8

Accepted Solution

by:
MartinCMS earned 42 total points
ID: 24851782
Great code pssandhu!

nazeemunnisa - you will need a where clause along with pssandhu code otherwise you will get an error for any column that does not contains '/' char.

UPDATE MyTable
SET Column = SUBSTRING(Column,1,CHARINDEX('/',Column)-1)
where CHARINDEX('/', Column) <> 0
0
 

Author Comment

by:nazeemunnisa
ID: 24857527
Thanks a lot for solution.  Between I'm also interested to know if there is some solution which can be done through pattern matching.
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 17

Assisted Solution

by:pssandhu
pssandhu earned 42 total points
ID: 24859212
Is this what you mean:

UPDATE MyTable
SET  Column = SUBSTRING(Column,1,PATINDEX('%/A%[0-9]', Column)-1)
WHERE  PATINDEX('%/A%[0-9]', Column) <> 0

Open in new window

0
 

Author Comment

by:nazeemunnisa
ID: 24860738
This would not work if the string ends with '/A' Ex:   387983483/A
0
 
LVL 41

Assisted Solution

by:ralmada
ralmada earned 41 total points
ID: 24860825
I would do it like this:
UPDATE yourTable
SET  yourColumn = LEFT(yourColumn,PATINDEX('%/A%', yourColumn)-1)
WHERE  PATINDEX('%/A%', yourColumn) <> 0

Open in new window

0
 
LVL 17

Expert Comment

by:pssandhu
ID: 24860827
<<In the above values, I want to remove/replace the values which end with '/A1', '/A2' ........  '/A9' >>
When I re-read your question I think you only wanted where /A was ending with a number.
Anyways, in that case wouldn't something like this work for you?

UPDATE MyTable
SET  Column = SUBSTRING(Column,1,PATINDEX('%/A%', Column)-1)
WHERE  PATINDEX('%/A%', Column) <> 0

Open in new window

0
 
LVL 41

Expert Comment

by:ralmada
ID: 25609531
I feel I've also contributed to this question.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 25609533
I feel I've also contributed to this question.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

734 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