TSQL Help
-
All,
I am trying to split a string down into 2 seperate strings using TSQL.
i.e.
name
Acrobat 7.0 Professional
DemoShield 7.5
name version
Acrobat 7.0 Professional
DemoShield 7.5
I basically want to split the first occurence of a number after a space in a string into 2 seperate strings.
Any help would be appreciated
Regards
Carl
-
RTRIM(LEFT([column],PATINDEX('%[0-9]%',[column])-1)) AS [name]
SUBSTRING([column],PATINDEX('%[0-9]%',[column]),255) AS [version]
Is it possible for the string not to have a number?
Far away is close at hand in the images of elsewhere.
Anon. -
or use
RTRIM(LEFT([column],PATINDEX('% [0-9]%',[column])-1)) AS [name]
SUBSTRING([column],PATINDEX('% [0-9]%',[column]),255) AS [version]
if you want to include a space before the number in case the name portion contains numbers
Far away is close at hand in the images of elsewhere.
Anon. -
I'm sorry David but I just don't see any difference between the 2 statements. Care to highlight it for me?
-
I had the same problem, but after a while I found the differenceRTRIM(LEFT([column],PATINDEX('%[0-9]%',[column])-1)) AS [name]
RTRIM(LEFT([column],PATINDEX('% [0-9]%',[column])-1)) AS [name]
Do you see the difference now that both statements are immediately adjacent, Remi? There is one extra space after the % sign.
Reminds me of "Where is Wally??" cartoons
-
Thanx Vlad.
Now here's the interesting part. How is david gonna highlight that space in red without using an underscore
? -
RTRIM(LEFT([column],PATINDEX('%[0-9]%',[column])-1)) AS [name]
RTRIM(LEFT([column],PATINDEX('% [0-9]%',[column])-1)) AS [name]
-
Ooops! Sorry about that
Far away is close at hand in the images of elsewhere.
Anon. -
...highlight that space in red ... 
didn't think of that
Far away is close at hand in the images of elsewhere.
Anon. -
Ok that was too easy for you guys
. -
Guys,
I get the following error when running the following command:
RTRIM(LEFT([name],PATINDEX('% [0-9]%',[name])-1)) AS [name],
SUBSTRING([name],PATINDEX('% [0-9]%',[name]),255) AS [version],
Invalid length parameter passed to the substring function.
Regards
Carl
-
It works for me, so it will probably be something in your data structure.
What is the length and datatype of the column you want to parse?
declare @name varchar (255)
set @name = 'SM Widows 3.1'
select RTRIM(LEFT(@name,PATINDEX('% [0-9]%',@name)-1)) AS [name]
select SUBSTRING(@name,PATINDEX('% [0-9]%',@name),255) AS [version]
name
---------------------------------------------------------------------------
SM Widows
(1 row(s) affected)
version
---------------------------------------------------------------------------
3.1
(1 row(s) affected)
If I change @name to varchar(10), I get precisely the same error message you did - so it will probably be something along this line.
-
The name column is VARCHAR(255).
-
You will get the error if the column does not contain a number preceeded by a space
if so try this
LEFT([name],PATINDEX('% [0-9]%',[name]+' 1')-1) AS [name],
SUBSTRING([name],PATINDEX('% [0-9]%',[name]+' 1')+1,255) AS [version]
Note the spaces (I did not colour them red)
Far away is close at hand in the images of elsewhere.
Anon. -
I don't mind for a single script. But it was hard to figure out the difference when they were not side by side
.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply