Solved

String Function

Posted on 2015-01-27
3
84 Views
Last Modified: 2015-01-28
Hello All,
We have a table which has data like below for a column ID
TEST300
TEST301
TEST302
TEST303
TEST304
How to extract data after 'test' like just 300,301,302,303,304.
Please help.
0
Comment
Question by:Star79
3 Comments
 
LVL 32

Assisted Solution

by:Daniel Wilson
Daniel Wilson earned 250 total points
ID: 40573636
Borrowing a bit of code from here ... http://www.experts-exchange.com/Database/Miscellaneous/A_17544-ORDER-BY-a-combined-value-text-numercial.html

I think this code snippet should do:
select cast(substring(ID, patindex('%[0-9]%', ID) , len(ID)) as int) as NumberPortion from MyTable

Open in new window

0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40573671
Substring(ID,5,999)
0
 
LVL 15

Accepted Solution

by:
Vikas Garg earned 250 total points
ID: 40574473
Hi,

Can try these ways
DECLARE @TS2 VARCHAR(50) = 'TEST'
SELECT SUBSTRING(Column,LEN(@TS2)+1,3),RIGHT(Column,3)

Open in new window

0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

816 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

13 Experts available now in Live!

Get 1:1 Help Now