NTEXT Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

NTEXT Help

Author

Topic

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 2014-09-09 : 09:19:03

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-09-09 : 09:39:29

SELECT CONVERT(NUMERIC(10,0) ,ISNULL(REPLACE(CAST(BUSPHONE as VARCHAR(MAX)),'-',''),'')) FROM DB

I did the following, but the above should work

CREATE TABLE #T
( N NTEXT )

INSERT INTO #T VALUES('555-555-5555')

SELECT CAST(ISNULL(REPLACE(CAST(N as VARCHAR(MAX)),'-',''),'') AS DECIMAL(10,0) ) FROM #T

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-09-09 : 09:43:25

Do something like this:

SELECT CAST(REPLACE(CAST(BUSPHONE AS NVARCHAR(MAX)),'-','') AS BIGINT) FROM DB

When converted to a number, the ten digit phone numbers are too large to be stored in an int, so use BIGINT. Also, when using VARCHAR/NVARCHAR, specify a length always.

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 2014-09-09 : 09:46:29

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 2014-09-09 : 09:49:38

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-09-09 : 10:15:10

I am thinking you have something other than a "-" in the ntext and that is your issue

Run The following to --1c then run the rest:

--1a
CREATE TABLE #T
( N NTEXT )
--1b
INSERT INTO #T VALUES('555-555-5555')

--1C
SELECT CAST(ISNULL(REPLACE(CAST(N as VARCHAR(MAX)),'-',''),'') AS DECIMAL(10,0) ) FROM #T

-- now run the rest
INSERT INTO #T VALUES('A555-555-5555')
SELECT CAST(ISNULL(REPLACE(CAST(N as VARCHAR(MAX)),'-',''),'') AS DECIMAL(10,0) ) FROM #T

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-09-09 : 11:11:07

I am thinking along the same lines as Michael - i.e., you may have some rows that are not numeric and not dashes. See if this query returns any rows:

SELECT * FROM DB WHERE
CAST(BUSPHONE AS VARCHAR(MAX)) LIKE ('%[^0-9-]%');

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources