Adding another step to the following

I have the following statement :
CONVERT(INT,( CONVERT (VARCHAR(4), SUBSTRING (terminatingcountrycode,
PATINDEX('%[^0]%', TerminatingCountryCode), LEN(TerminatingCountryCode)))+
CONVERT (VARCHAR(4), SUBSTRING(TerminatingIDDDCityCode, PATINDEX('%[^0]%',
TerminatingIDDDCityCode), LEN(TerminatingIDDDCityCode)))))
This runs perfectly.
However I just found out that the field actually contains two different
"types" of data. Example of the two data entries in the field are:
00000011 or
00000-12
What I need to add to the above statement is that if the data in the field
contain the '-', I need for it to be changed to a zero (0) and keep it so
that the results would look like
11 or
012
Thanks in advance... Will post sample data and table if neededOn Wed, 26 Jan 2005 08:07:04 -0800, scuba79 wrote:
(snip)
>What I need to add to the above statement is that if the data in the field
>contain the '-', I need for it to be changed to a zero (0) and keep it so
>that the results would look like
(snip)
Hi scuba79,
You can change any '-' to '0' with
REPLACE (terminatingcountrycode, '-', '0')
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Try,
select
case when patindex('%[^0-9]%', colA) > 0 then '0' + right(colA,
patindex('%[^0-9]%', reverse(colA)) - 1)
else cast(cast(colA as int) as varchar)
end
from
(
select '00000011'
union all
select '00000-12'
) as t(colA)
go
AMB
"scuba79" wrote:

> I have the following statement :
> CONVERT(INT,( CONVERT (VARCHAR(4), SUBSTRING (terminatingcountrycode,
> PATINDEX('%[^0]%', TerminatingCountryCode), LEN(TerminatingCountryCode)))+
> CONVERT (VARCHAR(4), SUBSTRING(TerminatingIDDDCityCode, PATINDEX('%[^0]%',
> TerminatingIDDDCityCode), LEN(TerminatingIDDDCityCode)))))
> This runs perfectly.
> However I just found out that the field actually contains two different
> "types" of data. Example of the two data entries in the field are:
> 00000011 or
> 00000-12
> What I need to add to the above statement is that if the data in the field
> contain the '-', I need for it to be changed to a zero (0) and keep it so
> that the results would look like
> 11 or
> 012
> Thanks in advance... Will post sample data and table if needed
>|||Or,
select
case when charindex('-', colA) > 0 then '0' + right(colA, charindex('-',
reverse(colA)) - 1)
else cast(cast(colA as int) as varchar)
end
from
(
select '00000011'
union all
select '00000-12'
) as t(colA)
go
AMB
"Alejandro Mesa" wrote:
> Try,
> select
> case when patindex('%[^0-9]%', colA) > 0 then '0' + right(colA,
> patindex('%[^0-9]%', reverse(colA)) - 1)
> else cast(cast(colA as int) as varchar)
> end
> from
> (
> select '00000011'
> union all
> select '00000-12'
> ) as t(colA)
> go
>
> AMB
>
> "scuba79" wrote:
>|||Or,
select
replace(coalesce(nullif(right(colA, charindex('-', reverse(colA))), ''),
cast(cast(colA as int) as varchar)), '-', '0')
from
(
select '00000011'
union all
select '00000-12'
) as t(colA)
go
AMB
"Alejandro Mesa" wrote:
> Or,
> select
> case when charindex('-', colA) > 0 then '0' + right(colA, charindex('-',
> reverse(colA)) - 1)
> else cast(cast(colA as int) as varchar)
> end
> from
> (
> select '00000011'
> union all
> select '00000-12'
> ) as t(colA)
> go
>
> AMB
> "Alejandro Mesa" wrote:
>|||Alejandro,
Your statement works great when I run it as a seperate statement and not
combined with the rest of the statement that I provided. I mistated my
question... The statement shows that there are two different fields being
changed. However, it's the field "terminatingidddcitycode" that has the two
different data entries that I need to find the solution for while still bein
g
able to use
"CONVERT(INT,( CONVERT (VARCHAR(4), SUBSTRING (terminatingcountrycode,
PATINDEX('%[^0]%', TerminatingCountryCode), LEN(TerminatingCountryCode)))+"
part, since that field will never contain any "-". Unless I totaling losing
it and not understanding your code
Thanks
scuba79
"Alejandro Mesa" wrote:
> Or,
> select
> replace(coalesce(nullif(right(colA, charindex('-', reverse(colA))), ''),
> cast(cast(colA as int) as varchar)), '-', '0')
> from
> (
> select '00000011'
> union all
> select '00000-12'
> ) as t(colA)
> go
>
> AMB
>
> "Alejandro Mesa" wrote:
>|||Can you provide some DDL (for those columns), sample data and expected resul
t?
AMB
"scuba79" wrote:
> Alejandro,
> Your statement works great when I run it as a seperate statement and not
> combined with the rest of the statement that I provided. I mistated my
> question... The statement shows that there are two different fields being
> changed. However, it's the field "terminatingidddcitycode" that has the t
wo
> different data entries that I need to find the solution for while still be
ing
> able to use
> "CONVERT(INT,( CONVERT (VARCHAR(4), SUBSTRING (terminatingcountrycode,
> PATINDEX('%[^0]%', TerminatingCountryCode), LEN(TerminatingCountryCode)))+
"
> part, since that field will never contain any "-". Unless I totaling losi
ng
> it and not understanding your code
> Thanks
> scuba79
> "Alejandro Mesa" wrote:
>|||On Wed, 26 Jan 2005 08:57:01 -0800, scuba79 wrote:

>Your statement works great when I run it as a seperate statement and not
>combined with the rest of the statement that I provided. I mistated my
>question... The statement shows that there are two different fields being
>changed. However, it's the field "terminatingidddcitycode" that has the tw
o
>different data entries that I need to find the solution for while still bei
ng
>able to use
>"CONVERT(INT,( CONVERT (VARCHAR(4), SUBSTRING (terminatingcountrycode,
>PATINDEX('%[^0]%', TerminatingCountryCode), LEN(TerminatingCountryCode)))+"
>part, since that field will never contain any "-". Unless I totaling losin
g
>it and not understanding your code
Hi scuba79,
If you take your original query and change each terminatingidddcitycode to
REPLACE (terminatingidddcitycode, '-','0'), it should work.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
By using the replace statement as you stated. When the entire statement
runs won't it strip that zero (0) '
Scuba79
"Hugo Kornelis" wrote:

> On Wed, 26 Jan 2005 08:57:01 -0800, scuba79 wrote:
>
> Hi scuba79,
> If you take your original query and change each terminatingidddcitycode to
> REPLACE (terminatingidddcitycode, '-','0'), it should work.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||Alejandro using the following:
CREATE TABLE [Table2] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[TerminatingCountryCode] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[OriginatingCountryCode] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[TerminatingIDDDCityCode] [varchar] (8) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[OriginatingIDDDCityCode] [varchar] (8) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
INSERT INTO Table2 (TerminatingCountryCode, OriginatingCountryCode,
TerminatingIDDDCityCode, OriginatingIDDDCityCode)
VALUES( '0052', '0000', '00000002','00000000')
INSERT INTO Table2 (TerminatingCountryCode, OriginatingCountryCode,
TerminatingIDDDCityCode, OriginatingIDDDCityCode)
VALUES( '0052', '0000', '00000-68','00000000')
INSERT INTO Table2 (TerminatingCountryCode, OriginatingCountryCode,
TerminatingIDDDCityCode, OriginatingIDDDCityCode)
VALUES( '007', '0000', '00000003','00000000')
INSERT INTO Table2 (TerminatingCountryCode, OriginatingCountryCode,
TerminatingIDDDCityCode, OriginatingIDDDCityCode)
VALUES( '0079', '0000', '00000-79','00000000')
The results that I looking for are when you combine fields
TerminatingCountryCode and TerminatingIDDDCityCode should be
522
52068
73
79079
Hope this helps and thank you for the assistance
Scuba79
"Alejandro Mesa" wrote:
> Can you provide some DDL (for those columns), sample data and expected res
ult?
>
> AMB
> "scuba79" wrote:
>