remove the characters and need to show only Numbers

Question

remove the characters and need to show only Numbers

kiran.rajenimbalkar

SSCrazy

Points: 2603
More actions
May 10, 2017 at 5:27 am

#325576

Hi Sir,
I need one help
CREATE TABLE TEMP (Name NVARCHAR(200))
INSERT INTO TEMP VALUES ('Abc Bbc (8172800)') INSERT INTO TEMP VALUES ('Sachin Tendulkar (1234567)') INSERT INTO TEMP VALUES ('Rhul Dravid (8796548)') INSERT INTO TEMP VALUES ('Xyz (5642189)') INSERT INTO TEMP VALUES ('Tom (2354897)')
From the above Temp table, I want to remove the characters and need to show only Numbers like below and its should be INT.
8172800
1234567
8796548
5642189
2354897
Please help me how to get such type of value as I have to use t his in my joining condition with another id
Regards,
Kiran R

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

there are a number of "StripNonNumeric" functions here on SSC scripts section:
http://www.sqlservercentral.com/search/?q=StripNonNumeric

I would strongly recommend either of the inline table value function that Sean Lange or Alan B posted int his thread: both are very knowledgable, accomplished posters, and I've been using some variants of what you see in that thread for years.
https://www.sqlservercentral.com/Forums/1585850/do-you-have-a-StripNonNumeric-ITVF-function

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Thomas Rushton SSC-Insane Points: 22649 More actions · Answer 2

If the "Name" field always contains a 7 digit number with a trailing bracket right before the end of the string, then you could just use a bit of basic string slicing:

SELECT LEFT(RIGHT(t.name, 8),7) FROM @temp t;

--edit--
missed out the int conversion...:

SELECT CONVERT(INT,LEFT(RIGHT(t.name, 8),7)) FROM @temp t;

Thomas Rushton
blog: https://thelonedba.wordpress.com

Phil Parkin SSC Guru Points: 246956 More actions · Answer 3

If your data is always in the very structured format your sample data shows, this works:

DROP TABLE IF EXISTS #Temp;

CREATE TABLE #Temp (Name NVARCHAR(200));

INSERT #Temp VALUES ('Abc Bbc (8172800)') ,('Sachin Tendulkar (1234567)') ,('Rhul Dravid (8796548)') ,('Xyz (5642189)') ,('Tom (2354897)');

SELECT t.Name , LEFT(RIGHT(t.Name, 8), 7) FROM #Temp t;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Phil Parkin SSC Guru Points: 246956 More actions · Answer 4

ThomasRushton - Wednesday, May 10, 2017 5:54 AM
If the "Name" field always contains a 7 digit number with a trailing bracket right before the end of the string, then you could just use a bit of basic string slicing:
SELECT LEFT(RIGHT(t.name, 8),7) FROM @temp t;
--edit--
missed out the int conversion...:
SELECT CONVERT(INT,LEFT(RIGHT(t.name, 8),7)) FROM @temp t;

Grrrr, you beat me to it!

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

kiran.rajenimbalkar SSCrazy Points: 2603 More actions · Answer 5

kiran.rajenimbalkar

SSCrazy

Points: 2603

May 10, 2017 at 6:19 am

#1941973

Thanks for your help, it works for me 🙂

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 6

Here's another option that will work in the number of numeric characters can vary...
-- Test Data -- IF OBJECT_ID('tempdb..#TEMP', 'U') IS NOT NULL DROP TABLE #TEMP;

CREATE TABLE #TEMP ( Name NVARCHAR(200) ); INSERT INTO #TEMP VALUES ('Abc Bbc (8172800)'); INSERT INTO #TEMP VALUES ('Sachin Tendulkar (1234567)'); INSERT INTO #TEMP VALUES ('Rhul Dravid (8796548)'); INSERT INTO #TEMP VALUES ('Xyz (5642189)'); INSERT INTO #TEMP VALUES ('Tom (2354897)'); INSERT INTO #TEMP VALUES ('Bill (235)'); INSERT INTO #TEMP VALUES ('Bob (235489733)');

--=======================================================

-- Solution -- SELECT Numbers = CAST(SUBSTRING(t.Name, s.NumStart, e.NumEnd) AS INT) FROM #TEMP t CROSS APPLY ( VALUES (CHARINDEX('(', t.Name) + 1) ) s (NumStart) CROSS APPLY ( VALUES (CHARINDEX(')', t.Name) - s.NumStart) ) e (NumEnd);