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

Question

1.00/5 (2 votes)

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 20-Mar-14 3:43am

ankit.agg1984

Updated 20-Mar-14 3:53am

Aarti Meswania

v3

Add a Solution

4 solutions

Add a Solution

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

Krunal Rohit · Answer 1 · 2014-03-20T06:10:00

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

Sergey Alexandrovich Kryukov · Answer 2 · 2014-03-20T05:17:00

Solution 3

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

Posted 20-Mar-14 5:17am

Sergey Alexandrovich Kryukov

Peter Leow · Answer 3 · 2014-03-20T04:26:00

Solution 2

Try this:

SQL

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.

Posted 20-Mar-14 4:26am

Peter Leow

Updated 20-Mar-14 4:50am

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

Aarti Meswania · Answer 4 · 2014-03-20T03:55:00

Solution 1

check link
http://stackoverflow.com/questions/9629880/extract-numbers-from-a-text-in-sql-server[^]
Happy Coding!
:)

Posted 20-Mar-14 3:55am

Aarti Meswania

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

4 solutions

Solution 4

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0

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

4 solutions

Solution 4

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us