Solved

How to replace the specific pattern in the string.

Posted on 2009-07-14
11
793 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
  • 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
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.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

708 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

12 Experts available now in Live!

Get 1:1 Help Now