September 5, 2013 at 11:19 am
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')
September 5, 2013 at 11:38 am
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
September 5, 2013 at 11:40 am
Strings are a pain in T-SQL.
Parsename, charindex, patindex, there are ways to do this, but it's harder than in other languages.
September 5, 2013 at 1:12 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 5, 2013 at 2:50 pm
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!
September 6, 2013 at 2:24 pm
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy