Add a character in 7th position in string. 1

[nutrotek]

Hello,

I have a string field 071106100. How do I add a "-" in the 7th position of the string so it looks like 071106-100?

 

[ptheriault]

select substring(field,1,7)+'-'+substring(filed,8,?)

replace the ? mark with the last character in the field size.

For example if you field is a varchar(30) it would be substring(filed,8,30)

- Paul
- Database performance looks fine, it must be the Network!

 

[gmmastros]

You could also show some sympathy for the often neglected function Stuff.

Declare @Test VarChar(20)

Set @Test = '071106100'

Select Stuff(@Test, 7, 0, '-')

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Catadmin]

Except STUFF deletes a specified length of characters and completely replaces it, which is different than using SubString to get it.

Still, this is the first time I've heard of this function. Thanks for the heads-up. I think I might actually use this one. @=)



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[gmmastros]

Catadmin,

You are correct. You can delete a specified number of characters from the string. In this case, we didn't want to delete anything, which is why I set the 3rd parameter = 0.

Truth is... I'm just showing off. I've never used Stuff in production code either.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ptheriault]

I'm thinking that George has BOL on audio and listens to it every night while sleeping! : )

- Paul
- Database performance looks fine, it must be the Network!

 

[Catadmin]

Insert into GeorgesBrain(Data, Data, Data)
(Select *
 From BOL 
 Where Info not in (Select Distinct Data
                    From GeorgesBrain)

You might be right. @=)



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[AlexCuse]

Hey Cat, you missed a parentheses ;-) What is that going to do to poor George's brain?

I think it's the other way around, and BOL actually refreshes from his brain each night.

Sorry, I get a little goofy on Friday mornings. Had to share

Ignorance of certain subjects is a great part of wisdom

 

[Catadmin]

Not only did I miss a paren, but I have three fields of the same name in the insert statement.

WHOOPS! Sorry, George. Didn't mean to mess up your data flow. @=)

I so want it to be 5:00 p.m. already. I have a severe case of weekend-itis. @=)



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"