Solved

How to strip unneeded characters

Posted on 2014-09-03
5
147 Views
Last Modified: 2014-09-04
How can I strip the unneeded characters from MS SQL table column to another view column based on the following examples:

[IDNo = 201]   to 1

[QuoteNo = 10005]  to 10005

[PONo = 3002] to 3002


Thanks.
0
Comment
Question by:JimiJ13
[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
5 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 450 total points
ID: 40300707
does this mean you just want the "number" information from a text field?
in which case, a simple search should have given some options already...
for example, this nice example;
http://blogs.lessthandot.com/index.php/datamgmt/datadesign/extracting-numbers-with-sql-server/
Create Function dbo.GetNumbers(@Data VarChar(8000))
Returns VarChar(8000)
AS
Begin	
    Return Left(
             SubString(@Data, PatIndex('%[0-9.-]%', @Data), 8000), 
             PatIndex('%[^0-9.-]%', SubString(@Data, PatIndex('%[0-9.-]%', @Data), 8000) + 'X')-1)
End

Open in new window

0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 40300709
You could use:
PATINDEX('%[0-9]%', my_column)

Open in new window

to find the character position that the number starts at, and use CHARINDEX (or PATINDEX again) to find the position of the ] character, then use SUBSTRING with the start and end positions to extract the number.

I'd write an SQL, but it's my bedtime.... zzzz
0
 

Assisted Solution

by:Monty Miller
Monty Miller earned 50 total points
ID: 40301165
You could query the column with  Left(SubString(BrandNum, PatIndex('%[0-9.-]%', BrandNum), 8000), PatIndex('%[^0-9.-]%', SubString(BrandNum, PatIndex('%[0-9.-]%', BrandNum), 8000) + 'X')-1)

Example:

SELECT     BrandNum, LEFT(SUBSTRING(BrandNum, PATINDEX('%[0-9.-]%', BrandNum), 8000), PATINDEX('%[^0-9.-]%', SUBSTRING(BrandNum, PATINDEX('%[0-9.-]%', BrandNum),
                      8000) + 'X') - 1) AS Data
FROM         tbl_Spirit_Brands

RESULTS:

BrandNum      Data
S10      10
S100      100
S1000      1000
S1004      1004
S1005      1005
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40301507
SELECT original_value,
    CASE WHEN pos_of_first_digit = 0 THEN '' ELSE
        SUBSTRING(original_value, pos_of_first_digit, PATINDEX('%[^0-9]%', SUBSTRING(original_value, pos_of_first_digit, 8000) + '~') - 1) END AS first_integer_found
FROM ( --supply test data; replace with your own table in final code
    SELECT '[IDNo = 201]' AS original_value UNION ALL
    SELECT '[QuoteNo = 10005]' UNION ALL
    SELECT '[PONo = 3002]' UNION ALL
    SELECT '[Unfinished = 999' UNION ALL
    SELECT '[all.data = 4125]'
) AS test_data
CROSS APPLY (
    SELECT PATINDEX('%[0-9]%', original_value) AS pos_of_first_digit
) AS assign_alias
0
 

Author Closing Comment

by:JimiJ13
ID: 40303199
Guy Hengel  solution is great and very clean.

Thanks!
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

In this article I will describe the Detach & Attach 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.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

705 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