Click here to Skip to main content
14,694,017 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hello,

How can we Split or Separate string and integer value of a column in sql server?

My column Value:-
Product Name
"Customized Kreation Wardrobe 1"
"Customized Kreation Wardrobe 2"
"Customized Kreation Wardrobe 3"
"Customized Kreation Wardrobe 4"
"Customized Kreation Wardrobe n"

I want to split integer from this value and create temporary column for integer.

Like:-
ProductName                        Id
Customized Kreation Wardrobe       1
Customized Kreation Wardrobe       2
Customized Kreation Wardrobe       3
Customized Kreation Wardrobe       4
Customized Kreation Wardrobe       n

How it can be possible?

Thanks in Advance.
Ankit Agarwal
Software Engineer
Posted
Updated 20-Mar-14 4:53am
v3

Try this:
SELECT
LEFT([product name], LEN([product name]) - CHARINDEX(' ', REVERSE([product name]))) AS productname,
RIGHT([product name], CHARINDEX(' ', REVERSE([product name])) - 1) AS id FROM table1

This will work for any length of string and number.
   
v3
Comments
[no name] 20-Mar-14 11:34am
   
Error:- Invalid length parameter passed to the RIGHT function.
Peter Leow 20-Mar-14 11:44am
   
Try here:
http://sqlfiddle.com/#!3/ec888/1
This is merely the consequence of the fact that you or someone abused the database. The column should be numeric if first place. The text part of your data should be in a separate column, or maybe it is not needed at all.

—SA
   
The problem you're talking is nothing but just the some raw data.. You can find the solution from other answers, but I'd suggest you to split it by yourself.

If there are n number of records, then you may certainly go for the other options but for few data, put the efforts :) That would be more appropriate.

-KR
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900