Solved

How to strip unneeded characters

Posted on 2014-09-03
5
137 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
5 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 450 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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:ScottPletcher
Comment Utility
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
Comment Utility
Guy Hengel  solution is great and very clean.

Thanks!
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

762 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

10 Experts available now in Live!

Get 1:1 Help Now