?
Solved

How to replace the specific pattern in the string.

Posted on 2009-07-14
11
Medium Priority
?
799 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 168 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 17

Assisted Solution

by:pssandhu
pssandhu earned 168 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 164 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

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.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
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…
Suggested Courses

752 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