Working with Strings

Question

Post reply

Working with Strings

dwilliscp

SSCrazy Eights

Points: 9270
More actions
September 5, 2013 at 11:19 am

#395427

From what I can find... it looks like working with strings is more of a headace .. in SQL than Pick. How do you test for a delimiter and then pull the string apart to show Code and Description?
With the data, provided below.. I would be looking to show the following:
Code Desc
4925208 alcohol
4921598 ethanol
01965
32001 Wax
Table (keeping it simple):
CREATE TABLE [dbo].[A_Test](
[Text_Code] [varchar](254) NOT NULL,
CONSTRAINT [PK_A_Test] PRIMARY KEY CLUSTERED
(
[Text_Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Data:
insert into A_Test (Text_Code)
values ('alcohol ~ 4925208'),('ethanol ~ 4921598'),('01965'),('Wax ~ 32001')

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

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

Something like this might help you. Unless it becomes more complicated

SELECT Text_Code,

LTRIM(PARSENAME( REPLACE( Text_Code, '~', '.'), 1)),

ISNULL(RTRIM(PARSENAME( REPLACE( Text_Code, '~', '.'), 2)), '')

FROM #A_Test

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Steve Jones - SSC Editor SSC Guru Points: 734669 More actions · Answer 2

Strings are a pain in T-SQL.

Parsename, charindex, patindex, there are ways to do this, but it's harder than in other languages.

ScottPletcher SSC Guru Points: 100961 More actions · Answer 3

I wouldn't use PARSENAME here because of potential side effects, for example, periods (.) in the data or brackets ([]) around a piece of data. The code below should be safe for all characters.

SELECT

LEFT(column_name, CHARINDEX('~', column_name + '~') - 1) AS first_value,

SUBSTRING(column_name, CHARINDEX('~', column_name + '~') + 1, 100) AS second_value

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

dwilliscp SSCrazy Eights Points: 9270 More actions · Answer 4

Thanks... yes this field is real simple.. just the code and description of the rail transport... So I tested the code on our data.. and it works great!

Greg Snidow SSCoach Points: 16106 More actions · Answer 5

Steve Jones - SSC Editor (9/5/2013)
Strings are a pain in T-SQL.

Steve, I must most humbly disagree. I find it is always fun to re-invent the wheel in just an ever so slightly differrent way than the last time I had to parse some strings that looked almost like the ones I have at present 🙂

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.