Truncating string Characters

Question

Post reply

Truncating string Characters

Ronnie Jones

Hall of Fame

Points: 3377
More actions
June 6, 2011 at 2:50 pm

#252272

Hello All,
I’ve been researching this all morning and so far have not come up with a solution and thought I’d reach out to the experts.
I have a column where I want to return the information in that column up to a certain character. Below is an example of the type of data that exist in that column:
Microsoft Windows Server 2003 R2 Standard Edition|C:\WINDOWS|\Device\Harddisk0\Partition1
Microsoft Windows XP Professional|C:\WINDOWS|\Device\Harddisk0\Partition2
I want the output to look like this:
Microsoft Windows Server 2003 R2 Standard Edition
Microsoft Windows XP Professional
In my thinking, I know I have to truncate the data in that column probably using the ‘%:\%’ as the character to truncate everything from the right back to that character. The “C” could change, so I can’t use that for all records. But I can’t find anything on truncating characters, only truncating a log.
Any recommendation on how I achieve this?
Thanks in advance for any help.
Ronnie

Viewing 5 posts - 1 through 4 (of 4 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Look up SUBSTRING, LEFT and CHARINDEX.

You're not finding anything for truncating a string, cause that's not what this is called.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

You might want to test this.

DECLARE @String VARCHAR(200)

SET @String = 'Microsoft Windows Server 2003 R2 Standard Edition|C:\WINDOWS|\Device\Harddisk0\Partition1'

SELECT SUBSTRING(@String,1,PATINDEX ( '%|%' , @String) -1)

Result:

Microsoft Windows Server 2003 R2 Standard Edition

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

jarid.lawson SSCrazy Points: 2953 More actions · Answer 3

I'm sure the experts have a faster way to do it, but this is what I came up with:

If Object_ID('dbo.StringTest') Is Not Null

Drop Table dbo.StringTest

Go

--------------------

Select 'Microsoft Windows Server 2003 R2 Standard Edition|C:\WINDOWS|\Device\Harddisk0\Partition1' As CheckString

Into dbo.StringTest

Union All

Select 'Microsoft Windows XP Professional|C:\WINDOWS|\Device\Harddisk0\Partition2'

Union All

Select 'Microsoft Windows :XP Professional|C:\WINDOWS|\Device\Harddisk0\Partition2'

Go

--------------------

Select SubString(CheckString,1,Len(SubString(CheckString,1,CharIndex(':\',CheckString)-3)))

From dbo.StringTest

Go

--------------------

Drop Table dbo.StringTest

Go

Results Set:

Microsoft Windows Server 2003 R2 Standard Edition

Microsoft Windows XP Professional

Microsoft Windows :XP Professional

[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein

"DOH!"
- Homer Simpson[/font]

Ronnie Jones Hall of Fame Points: 3377 More actions · Answer 4

Thank you all for your help and suggestions. This is good info. I am researching the functions that you've suggested to get a better understanding and will now add them to my arsenal of SQL techniques.

Thanks again!!

Ronnie