Inserting a hyphen (-) into the 3rd position of a

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

SQL Server 2000 Forums

Transact-SQL (2000)

Inserting a hyphen (-) into the 3rd position of a

Author

Topic

koln5
Starting Member

29 Posts

Posted - 2004-09-13 : 11:06:34

I have a colum (Column A) of numbers, whose datatype is nvarchar;

Column A
157853
4389
1999999999556
4568987

I need to insert a hyphen, 3 spaces from the right (to left) My numbers would look like this:

157-853
4-389
1999999999-556
4568-987

...do I use the rtrim function ?...what would the syntax look like ?

Thank you

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-13 : 11:14:35

select col1, substring(col1, 1, len(col1)-3) + '-' + right(col1, 3)
from MyTable

Go with the flow & have fun! Else fight the flow

koln5
Starting Member

29 Posts

Posted - 2004-09-13 : 12:25:26

Great..that works BUT...I failed to mention that I only want a hyphen inserted into the third position only if a hyphen is not present..what would be the rest of the syntax ?

Sorry...and thank you so much

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-13 : 12:32:32

this should do:


select col1, 	case when charindex('-', col1) <> len(col1)-3
			then substring(col1, 1, len(col1)-3) + '-' + right(col1, 3)
			else col1
		end
from MyTable

Go with the flow & have fun! Else fight the flow

koln5
Starting Member

29 Posts

Posted - 2004-09-13 : 12:54:20

looks like it still inserts a '-' even if one already exists...numbers are coming out like this:

100--5
123--4

any other ideas ?

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-09-13 : 14:08:24

How about this:
[CODE]
select col1, case when len(col1) > 4 and substring(col1, len(col1) - 3, 1) <> '-' then
left(col1, len(col1) - 3) + '-' + right(col1, 3) else
col1 end
from mytable
[/CODE]

Duane.

koln5
Starting Member

29 Posts

Posted - 2004-09-13 : 15:02:00

sorry, same result....still two hyphens showing up if one already exists.

I really appreciate your time

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-09-13 : 15:12:10

well not when I test it like this:
[CODE]
create table mytable(col1 varchar(100))
insert into mytable
select 157853 union
select 4389 union
select 1999999999556 union
select 4568987 union
select '157-853' union
select '4-389' union
select '1999999999-556' union
select '4568-987'
go

select col1, case when len(col1) > 4 and substring(col1, len(col1) - 3, 1) <> '-' then
left(col1, len(col1) - 3) + '-' + right(col1, 3) else
col1 end
from mytable

[/CODE]

Post some of your data

Duane.

n/a
deleted

35 Posts

Posted - 2004-09-13 : 15:31:39

Try this


case when charindex('-', col1) = 0 then col1 else left(col1, len(col1) - 3) + '-' + right(col1, 3) end

the only difference is that it tests to see if '-' exists instead of testing to see if it exists in the third to last position.

HTH
Paul

koln5
Starting Member

29 Posts

Posted - 2004-09-13 : 17:25:37

Duane: here is my data

CREATE TABLE [dbo].[Main1] (
[Leasenumber] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

Values for Leasenumber;

10013532
1002-04
1004-03
10013577
10103721

koln5
Starting Member

29 Posts

Posted - 2004-09-13 : 17:54:33

What's really crazy is that when I run your sql syntax:

select leasenumber, case when substring(leasenumber, len(leasenumber) - 3, 1) <> '-' then
left(leasenumber, len(leasenumber) - 3) + '-' + right(leasenumber, 3) else
leasenumber end
from main1

I get the following inconsistant results..the first,second, and fourth row are perfect...but the third puts in a '-', even though one is already present

Leasenumber (no name)
1001189-897 1001189-897
5656565665655 5656565665-655
10003-21 10003--21
4645645654456546 4645645654456-546

I'm going crazy over here....

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-13 : 22:56:22

use patindex instead of charindex so you can use wildcards:

using duane's example:

declare @mytable table (col1 varchar(100))

insert into @mytable
select 157853 union
select 4389 union
select 1999999999556 union
select 4568987 union
select '157-853' union
select '4-389' union
select '1999999999-556' union
select '4568-987'

select col1, case when patindex('%-%',col1)= 0 then
substring(col1, 1, len(col1)-3) + '-' + right(col1, 3)
else
col1 end
from @mytable

koln5
Starting Member

29 Posts

Posted - 2004-09-14 : 16:06:24

EXCELLENT !...thats it !

Thank you Jen...and to all the others for your time...I really appreciate it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources