Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


substring


substring

Author
Message
ny66
ny66
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 32
i am using the following to parse data from a string, but it only works properly if the values are in the exact place in the string.

SELECT SUBSTRING( f1, 1, 4 ) as style,
SUBSTRING( f1, 17, 9 ) as color,
SUBSTRING( f1, 34, 3 ) as size_1,
SUBSTRING( f1, 37, 3 ) as size_2
from dbo.NEWDUNDD_SEACX

the following is what my string looks like where i am trying to retrieve the values.

24W 529 22 132 110 123 -13 516 (one row of data)

3102 DENIM 8 99 0 213 213 0 213 312 (another row data)

66552266 green blue 77p 1111 2222 33333 (another row)

you see that they are never exactly in the same place. I would like to know if there is a way to retrieve the records based upon a space between the values.
rvasanth
rvasanth
SSC Veteran
SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)

Group: General Forum Members
Points: 299 Visits: 114
You can split the string by ' ' (single space) the you can take the second value for colour. similarly fo others
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14933 Visits: 38924
here's an example using a cvustom function CHARINDEX2, which finds the nth occurrance of a string...in your case a space...

/*
Example:
SELECT dbo.CHARINDEX2('a', 'abbabba', 3)
returns the location of the third occurrence of 'a'
which is 7
*/
CREATE FUNCTION CHARINDEX2(
@TargetStr varchar(8000),
@SearchedStr varchar(8000),
@Occurrence int)
RETURNS int
AS
BEGIN
DECLARE @pos int, @counter int, @ret int
SET @pos = CHARINDEX(@TargetStr, @SearchedStr)
SET @counter = 1
IF @Occurrence = 1
SET @ret = @pos
ELSE
BEGIN
WHILE (@counter < @Occurrence)
BEGIN
SELECT @ret = CHARINDEX(@TargetStr, @SearchedStr, @pos + 1)
SET @counter = @counter + 1
SET @pos = @ret
END
END
RETURN(@ret)
END
GO
/*
---------------------------------------- ---------------------------------------- ----------------------------------------
24W 529 22
3102 DENIM 8
66552266 green blue
*/
SELECT
SUBSTRING(TheExample,1,dbo.CHARINDEX2(' ', TheExample, 1)),
SUBSTRING(TheExample,dbo.CHARINDEX2(' ', TheExample, 1),dbo.CHARINDEX2(' ', TheExample, 2) - dbo.CHARINDEX2(' ', TheExample, 1)),
SUBSTRING(TheExample,dbo.CHARINDEX2(' ', TheExample, 2),dbo.CHARINDEX2(' ', TheExample, 3) - dbo.CHARINDEX2(' ', TheExample, 2))
from (
SELECT '24W 529 22 132 110 123 -13 516 ' AS TheExample UNION ALL
SELECT '3102 DENIM 8 99 0 213 213 0 213 312 ' UNION ALL
SELECT '66552266 green blue 77p 1111 2222 33333 '
) X





Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ny66
ny66
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 32
thank you very much, that is exactly what i am looking for to retrieve the data to columns,

how do i create a table from that, can i just replace create function with create table?

do i need to build a table from the function?
ny66
ny66
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 32
Some additional assistance please. I used this to create a function, but how do i get it to a table? i have done some searching but im stumped. it creates a function.

any guidance will be appreciated. thanks


CREATE FUNCTION DBO.test(
@TargetStr varchar(8000),
@SearchedStr varchar(8000),
@Occurrence int)
RETURNS int
AS
BEGIN
DECLARE @pos int, @counter int, @ret int
SET @pos = CHARINDEX(@TargetStr, @SearchedStr)
SET @counter = 1
IF @Occurrence = 1
SET @ret = @pos
ELSE
BEGIN
WHILE (@counter < @Occurrence)
BEGIN
SELECT @ret = CHARINDEX(@TargetStr, @SearchedStr, @pos + 1)
SET @counter = @counter + 1
SET @pos = @ret
END
END
RETURN(@ret)
END
GO
/*
---------------------------------------- ---------------------------------------- ----------------------------------------
24W 529 22
3102 DENIM 8
66552266 green blue
*/
SELECT
SUBSTRING(TheExample,1,dbo.CHARINDEX2(' ', TheExample, 1)),
SUBSTRING(TheExample,dbo.CHARINDEX2(' ', TheExample, 1),dbo.CHARINDEX2(' ', TheExample, 2) - dbo.CHARINDEX2(' ', TheExample, 1)),
SUBSTRING(TheExample,dbo.CHARINDEX2(' ', TheExample, 2),dbo.CHARINDEX2(' ', TheExample, 3) - dbo.CHARINDEX2(' ', TheExample, 2))
from (
SELECT 'F1' AS TheExample
FROM dbo.NEWDUNDD_SEACX/*UNION ALL
SELECT '3102 DENIM 8 99 0 213 213 0 213 312 ' UNION ALL
SELECT '66552266 green blue 77p 1111 2222 33333 '*/
) X
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14933 Visits: 38924
originally you had posted that you had a table dbo.NEWDUNDD_SEACX with a column f1

because you had no CREATE TABLE or INSERT INTO scripts, i created my own table wth the column...TheExample
i think you are looking for something like this? you could create a view with code similar to this.

SELECT
style = SUBSTRING(f1,1,dbo.CHARINDEX2(' ', f1, 1)),
color = SUBSTRING(f1,dbo.CHARINDEX2(' ', f1, 1),dbo.CHARINDEX2(' ', f1, 2) - dbo.CHARINDEX2(' ', f1, 1)),
size_1 = SUBSTRING(f1,dbo.CHARINDEX2(' ', f1, 2),dbo.CHARINDEX2(' ', f1, 3) - dbo.CHARINDEX2(' ', f1, 2)),
size_2 = SUBSTRING(f1,dbo.CHARINDEX2(' ', f1, 3),dbo.CHARINDEX2(' ', f1, 4) - dbo.CHARINDEX2(' ', f1, 3))
from dbo.NEWDUNDD_SEACX



Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ny66
ny66
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 32
Thanks, that is basically what i am looking for, i apologize for being slow on getting this, but i want to understand as well as, get it working.

the first substring picks up the first values, but then the other columns are blank,

SELECT
style = SUBSTRING(f1,1,dbo.CHARINDEX2(' ', f1, 1)),-- this picks up the first values in the string.

the following are all blank.

color = SUBSTRING(f1,dbo.CHARINDEX2(' ', f1, 1),dbo.CHARINDEX2(' ', f1, 2) - dbo.CHARINDEX2(' ', f1, 1)),
size_1 = SUBSTRING(f1,dbo.CHARINDEX2(' ', f1, 2),dbo.CHARINDEX2(' ', f1, 3) - dbo.CHARINDEX2(' ', f1, 2)),
size_2 = SUBSTRING(f1,dbo.CHARINDEX2(' ', f1, 3),dbo.CHARINDEX2(' ', f1, 4) - dbo.CHARINDEX2(' ', f1, 3))
from dbo.NEWDUNDD_SEACX

if i understand properly, in the substring it is looking at the blank space, column, position ('', f1, 1) so it should find the blank space after the position, then search the column again starting after the blank space at postition 2???

hope this makes sense.
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14933 Visits: 38924
yes that's correct...but it depends on your data as to whether it will be blank or find a value...i assumeed one space between each ite,.

if there are TWO spaces between each element in your data, it would probably find every other item..3 spaces between each, then it would find nothing but the first item in our example.
you'll need to look at the actual data in f1: what does the column actually contain?

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

turbo2mx
turbo2mx
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 19
you should try whit the function replace, maybe its the solution for your problem.

I can see that your´s strings have a variable lenght, you should try to delimite each substring like you need.

select REPLACE('24W 529 22 132 110 123 -13 516',' ','')

select REPLACE('3102 DENIM 8 99 0 213 213 0 213 312',' ','')

select REPLACE('66552266 green blue 77p 1111 2222 33333',' ','')

See you
ny66
ny66
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 32
if i use replace, i still cant get the values in the columns i need due to length of the field. there is no set length on the first value it hits.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search