Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

substring Expand / Collapse
Author
Message
Posted Monday, August 9, 2010 7:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 23, 2010 10:31 AM
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.
Post #965957
Posted Monday, August 9, 2010 8:03 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, January 10, 2013 6:16 AM
Points: 299, Visits: 114
You can split the string by ' ' (single space) the you can take the second value for colour. similarly fo others
Post #965971
Posted Monday, August 9, 2010 9:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:20 AM
Points: 12,927, Visits: 32,332
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #966031
Posted Monday, August 9, 2010 9:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 23, 2010 10:31 AM
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?

Post #966045
Posted Wednesday, August 11, 2010 8:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 23, 2010 10:31 AM
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

Post #967436
Posted Wednesday, August 11, 2010 8:24 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:20 AM
Points: 12,927, Visits: 32,332
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #967451
Posted Wednesday, August 11, 2010 8:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 23, 2010 10:31 AM
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.
Post #967475
Posted Wednesday, August 11, 2010 10:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:20 AM
Points: 12,927, Visits: 32,332
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #967570
Posted Thursday, August 12, 2010 7:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 14, 2012 10:32 AM
Points: 2, Visits: 17
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
Post #968172
Posted Tuesday, August 17, 2010 10:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 23, 2010 10:31 AM
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.
Post #970598
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse