Removing spaces between words in sql

I guess there is no built in functions to do this but I have a function that replaces anything that is not A-Z with a space and returns @.data. What I additionally need the function to do is scrunch up @.data (remove all blanks betwwen each word so that 'I ran very fast' would be 'Iranveryfast').

What I need help in doing is the "Scrunch" part. Is there a way I could move the @.Data to something like @.DataHold and inspect each character, if it is not a blank, move that character back to @.Data?
This was pretty easy for me to do in C# with a while loop, but I do not know how to get it done in SQL Server 2005.

Thanks for any help!

Use the replace function.

replace( @.Data, ' ', '' )

For example,

DECLARE @.Data varchar(1000)
SET @.Data = 'I ran very fast'

SELECT replace( @.Data, ' ', '' )

Iranveryfast

Removing rows from a result

I have a query which returns the following results. What i want to do
is remove any rows where col1 does not appear in col2. e.g. "107". As
well as this i want to remove any rows that are affected by this, e.g.
"109" as once 107 goes, 109 does not appear in col2.
Thanks in advance.
col1 col2
1 108
1 112
1 115
1 116
1 117
1 118
107 109
108 114
109 110
110 111
112 113
118 119On Apr 3, 3:45 pm, danielev...@.gmail.com wrote:
> I have a query which returns the following results. What i want to do
> is remove any rows where col1 does not appear in col2. e.g. "107". As
> well as this i want to remove any rows that are affected by this, e.g.
> "109" as once 107 goes, 109 does not appear in col2.
> Thanks in advance.
> col1 col2
> 1 108
> 1 112
> 1 115
> 1 116
> 1 117
> 1 118
> 107 109
> 108 114
> 109 110
> 110 111
> 112 113
> 118 119
Try something like this
drop table #temp
drop table #temp1
create table #temp (col1 int, col2 int )
insert into #temp values (1, 108 )
insert into #temp values (1, 112 )
insert into #temp values (1, 115 )
insert into #temp values (1, 116 )
insert into #temp values (1, 117 )
insert into #temp values (1 ,118 )
insert into #temp values (107,109 )
insert into #temp values (108,114 )
insert into #temp values (109,110 )
insert into #temp values (110,111 )
insert into #temp values (112,113 )
insert into #temp values (118,119 )
insert into #temp values (118,1 )
create table #temp1 (col1 int, col2 int )
while 1 = 1
begin
truncate table #temp1
insert into #temp1 select * from #temp a where not exists (select 1
from #temp b where a.col1 = b.col2 )
if @.@.rowcount = 0
break
delete #temp from #temp
inner join #temp1 on #temp.col1 = #temp1.col1
end
select * from #temp|||On Apr 3, 12:59 pm, "M A Srinivas" <masri...@.gmail.com> wrote:
> On Apr 3, 3:45 pm, danielev...@.gmail.com wrote:
>
> > I have a query which returns the following results. What i want to do
> > is remove any rows where col1 does not appear in col2. e.g. "107". As
> > well as this i want to remove any rows that are affected by this, e.g.
> > "109" as once 107 goes, 109 does not appear in col2.
> > Thanks in advance.
> > col1 col2
> > 1 108
> > 1 112
> > 1 115
> > 1 116
> > 1 117
> > 1 118
> > 107 109
> > 108 114
> > 109 110
> > 110 111
> > 112 113
> > 118 119
> Try something like this
> drop table #temp
> drop table #temp1
> create table #temp (col1 int, col2 int )
> insert into #temp values (1, 108 )
> insert into #temp values (1, 112 )
> insert into #temp values (1, 115 )
> insert into #temp values (1, 116 )
> insert into #temp values (1, 117 )
> insert into #temp values (1 ,118 )
> insert into #temp values (107,109 )
> insert into #temp values (108,114 )
> insert into #temp values (109,110 )
> insert into #temp values (110,111 )
> insert into #temp values (112,113 )
> insert into #temp values (118,119 )
> insert into #temp values (118,1 )
> create table #temp1 (col1 int, col2 int )
> while 1 = 1
> begin
> truncate table #temp1
> insert into #temp1 select * from #temp a where not exists (select 1
> from #temp b where a.col1 = b.col2 )
> if @.@.rowcount = 0
> break
> delete #temp from #temp
> inner join #temp1 on #temp.col1 = #temp1.col1
> end
> select * from #temp
Thanks for this. Is this the only way to achieve this result? I was
hoping to do it in the initial query without using temp tables.
Dan

Removing rows from a result

I have a query which returns the following results. What i want to do
is remove any rows where col1 does not appear in col2. e.g. "107". As
well as this i want to remove any rows that are affected by this, e.g.
"109" as once 107 goes, 109 does not appear in col2.
Thanks in advance.
col1 col2
1108
1112
1115
1116
1117
1118
107109
108114
109110
110111
112113
118119
On Apr 3, 3:45 pm, danielev...@.gmail.com wrote:
> I have a query which returns the following results. What i want to do
> is remove any rows where col1 does not appear in col2. e.g. "107". As
> well as this i want to remove any rows that are affected by this, e.g.
> "109" as once 107 goes, 109 does not appear in col2.
> Thanks in advance.
> col1 col2
> 1 108
> 1 112
> 1 115
> 1 116
> 1 117
> 1 118
> 107 109
> 108 114
> 109 110
> 110 111
> 112 113
> 118 119
Try something like this
drop table #temp
drop table #temp1
create table #temp (col1 int, col2 int )
insert into #temp values (1, 108 )
insert into #temp values (1, 112 )
insert into #temp values (1, 115 )
insert into #temp values (1, 116 )
insert into #temp values (1, 117 )
insert into #temp values (1 ,118 )
insert into #temp values (107,109 )
insert into #temp values (108,114 )
insert into #temp values (109,110 )
insert into #temp values (110,111 )
insert into #temp values (112,113 )
insert into #temp values (118,119 )
insert into #temp values (118,1 )
create table #temp1 (col1 int, col2 int )
while 1 = 1
begin
truncate table #temp1
insert into #temp1 select * from #temp a where not exists (select 1
from #temp b where a.col1 = b.col2 )
if @.@.rowcount = 0
break
delete #temp from #temp
inner join #temp1 on #temp.col1 = #temp1.col1
end
select * from #temp
|||On Apr 3, 12:59 pm, "M A Srinivas" <masri...@.gmail.com> wrote:
> On Apr 3, 3:45 pm, danielev...@.gmail.com wrote:
>
>
>
> Try something like this
> drop table #temp
> drop table #temp1
> create table #temp (col1 int, col2 int )
> insert into #temp values (1, 108 )
> insert into #temp values (1, 112 )
> insert into #temp values (1, 115 )
> insert into #temp values (1, 116 )
> insert into #temp values (1, 117 )
> insert into #temp values (1 ,118 )
> insert into #temp values (107,109 )
> insert into #temp values (108,114 )
> insert into #temp values (109,110 )
> insert into #temp values (110,111 )
> insert into #temp values (112,113 )
> insert into #temp values (118,119 )
> insert into #temp values (118,1 )
> create table #temp1 (col1 int, col2 int )
> while 1 = 1
> begin
> truncate table #temp1
> insert into #temp1 select * from #temp a where not exists (select 1
> from #temp b where a.col1 = b.col2 )
> if @.@.rowcount = 0
> break
> delete #temp from #temp
> inner join #temp1 on #temp.col1 = #temp1.col1
> end
> select * from #temp
Thanks for this. Is this the only way to achieve this result? I was
hoping to do it in the initial query without using temp tables.
Dan

Removing rows from a result

I have a query which returns the following results. What i want to do
is remove any rows where col1 does not appear in col2. e.g. "107". As
well as this i want to remove any rows that are affected by this, e.g.
"109" as once 107 goes, 109 does not appear in col2.
Thanks in advance.
col1 col2
1 108
1 112
1 115
1 116
1 117
1 118
107 109
108 114
109 110
110 111
112 113
118 119On Apr 3, 3:45 pm, danielev...@.gmail.com wrote:
> I have a query which returns the following results. What i want to do
> is remove any rows where col1 does not appear in col2. e.g. "107". As
> well as this i want to remove any rows that are affected by this, e.g.
> "109" as once 107 goes, 109 does not appear in col2.
> Thanks in advance.
> col1 col2
> 1 108
> 1 112
> 1 115
> 1 116
> 1 117
> 1 118
> 107 109
> 108 114
> 109 110
> 110 111
> 112 113
> 118 119
Try something like this
drop table #temp
drop table #temp1
create table #temp (col1 int, col2 int )
insert into #temp values (1, 108 )
insert into #temp values (1, 112 )
insert into #temp values (1, 115 )
insert into #temp values (1, 116 )
insert into #temp values (1, 117 )
insert into #temp values (1 ,118 )
insert into #temp values (107,109 )
insert into #temp values (108,114 )
insert into #temp values (109,110 )
insert into #temp values (110,111 )
insert into #temp values (112,113 )
insert into #temp values (118,119 )
insert into #temp values (118,1 )
create table #temp1 (col1 int, col2 int )
while 1 = 1
begin
truncate table #temp1
insert into #temp1 select * from #temp a where not exists (select 1
from #temp b where a.col1 = b.col2 )
if @.@.rowcount = 0
break
delete #temp from #temp
inner join #temp1 on #temp.col1 = #temp1.col1
end
select * from #temp|||On Apr 3, 12:59 pm, "M A Srinivas" <masri...@.gmail.com> wrote:
> On Apr 3, 3:45 pm, danielev...@.gmail.com wrote:
>
>
>
>
> Try something like this
> drop table #temp
> drop table #temp1
> create table #temp (col1 int, col2 int )
> insert into #temp values (1, 108 )
> insert into #temp values (1, 112 )
> insert into #temp values (1, 115 )
> insert into #temp values (1, 116 )
> insert into #temp values (1, 117 )
> insert into #temp values (1 ,118 )
> insert into #temp values (107,109 )
> insert into #temp values (108,114 )
> insert into #temp values (109,110 )
> insert into #temp values (110,111 )
> insert into #temp values (112,113 )
> insert into #temp values (118,119 )
> insert into #temp values (118,1 )
> create table #temp1 (col1 int, col2 int )
> while 1 = 1
> begin
> truncate table #temp1
> insert into #temp1 select * from #temp a where not exists (select
1
> from #temp b where a.col1 = b.col2 )
> if @.@.rowcount = 0
> break
> delete #temp from #temp
> inner join #temp1 on #temp.col1 = #temp1.col1
> end
> select * from #temp
Thanks for this. Is this the only way to achieve this result? I was
hoping to do it in the initial query without using temp tables.
Dan

Removing individual results from a paged set of results.

Hi,

I have a web form that lets users search for people in my database they wish to contact. The database returns a paged set of results using a CTE, Top X, and Row_number().

I would like to give my users to option of removing individual people from this list but cannot find a way to do this.

I have tried creating a session variable with a comma delimited list of ID's that I pass to my sproc and use in a NOT IN() statement. But I keep getting a "Input string was not in a correct format." Error Message.

Is there any way to do this? I am still new to stored procedures so any advice would be helpful.

Thanks

The only way I know to do it is to use dynamic sql.

|||

Can you post your SQL stored procedure? Its a lot easier to give suggestions if we can see what we're working with. There are several ways to do this, but I'd like to see what I'm working with before I make a suggestion.

Stu

|||

This is the SPROC I am currently working with without any code to remove individual rows.

CREATE PROCEDURE [dbo].[zk_update_request_england]
(
@.property_type tinyint,
@.market_status tinyint,
@.price int,
@.bedrooms tinyint,
@.search_location varchar(30),
@.PageSize int,
@.PageIndex int,
@.TRV int,
@.topx int,
@.TotalRequests int OUTPUT
)

AS

SET NOCOUNT ON

BEGIN

-- Set Paging limits
Declare @.firstRow Int;
Declare @.lastRow Int;
Set @.firstRow = (@.PageIndex * @.PageSize) + 1;
Set @.lastRow = @.firstRow + @.PageSize - 1;

-- Load count into @.TotalRequests
Set @.TotalRequests = (SELECT Count(*)
FROM [dbo].[zk_request_england]
WHERE property_type = @.property_type
AND market_status = @.market_status
AND bedrooms <= @.bedrooms
AND search_location = @.search_location
AND min_price <= @.price
AND max_price >= @.price)

-- If @.TotalRequests is less than @.topx change @.topx to @.TotalRequests
IF @.TotalRequests <= @.topx
SET @.topx = @.TotalRequests
ELSE
SET @.TotalRequests = @.topx
;

WITH SearchResults AS
(
SELECT TOP(@.topx) ROW_NUMBER() OVER (ORDER BY max_price DESC) AS RowNumber,
id,
user_name,
bedrooms,
min_price,
max_price,
property_description,
searched
FROM [dbo].[zk_request_england]
WHERE property_type = @.property_type
AND market_status = @.market_status
AND bedrooms <= @.bedrooms
AND search_location = @.search_location
AND min_price <= @.price
AND max_price >= @.price
)

SELECT id,
user_name,
bedrooms,
min_price,
max_price,
property_description
FROM SearchResults
WHERE RowNumber BETWEEN @.firstRow and @.lastRow

END

SET NOCOUNT OFF

removing duplicate entries in SQL Field

Hi All,

Below is a snippet of MS SQL inside some VB that retieves
Commodity info such as product names and related information and returns the results in an ASP Page. My problem is that with certain searches, elements returned in the synonym field repeat. For instance, on a correct search I get green, red, blue, and yellow which is correct. On another similar search with different commodity say for material, I get Plastic, Glass,Sand - Plastic, Glass,Sand - Plastic, Glass, Sand. I want to remove the repeating elements returned in this field. IOW, I just need one set of Plastic, Glass and Sand. I hope this makes sense.

Below is the SQL and the results from the returned page.

PS I tried to use distinct but with no luck I want just one of each in the example below.

Thanks in Advance!

Scott

==============================

SQL = ""
SQL = "SELECT B.CIMS_MSDS_NUM," & _
"A.COMMODITY_NUMBER, " & _
"B.CIMS_TRADE_NME," & _
"B.CIMS_MFR_NME," & _
"B.CIMS_MSDS_PREP_DTE," & _
"B.APVL_CDE," & _
"COALESCE(C.REGDMATLCD,'?') AS DOTREGD," & _
"COALESCE( D.CIMS_TRADE_SYNM,'NO SYNONYMS') AS SYNONYM, " & _
"A.MSDS_CMDTY_VERIF, " & _
"A.CATALOG_ID " & _
"FROM ( MATEQUIP.VMSDS_CMDTY A " & _
" RIGHT OUTER JOIN MATEQUIP.VCIMS_TRD_PROD_INF B " & _
" ON A.CIMS_MSDS_NUM = B.CIMS_MSDS_NUM " & _
" LEFT OUTER JOIN MATEQUIP.VDOT_TRADE_PROD C " & _
" ON A.CIMS_MSDS_NUM = C.MSDSNUM " & _
" LEFT OUTER JOIN MATEQUIP.VCIMS_TRD_PROD_SYN D " & _
" ON B.CIMS_MSDS_NUM = D.CIMS_MSDS_NUM) "

SQL1 = ""
SQL1 = SQL

SQL = SQL & "WHERE " & Where & " "
==================================

Here is a piece of the problem field, note repeating colors etc.

CCM-PAINTS & COATINGS (1/26/98)F65E36 ORANGE F65W1 GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEF65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65E36 ORANGE F65W1 GLOSS WHITEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65E36 ORANGE F65W1 GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65A1 WARM GRAY F65L6 TURQUOISEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65B1 GLOSS BLACK F65N11

Hi,

if you are using denormalized stored values, you will first have denormalized them to use SQL Server functions for grouping / distinct them. You will have to separate the values first using a separator and using a function like this one here:

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

removing chr(13) and chr(10) from SQL

Hi.

Is there a way to remove carriage returns and line feeds from a field in SQL.

Here is what I have at the moment:

CAST(m.Description AS VARCHAR(8000))AS "Product Description"

(have had to cast as VarChar because of problems with aggregate functions in rest of query).

Problem is that this m.description field has carriage returns and line breaks in it .. how do I remove these inside the SQL statement?

thanksHave you tried REPLACE ( StringExpression1 , StringExpression2 , StringExpression3 ) T-SQL function?|||I tried something like this:

CAST(replace(m.Description, vlbf, ' ') AS VARCHAR(8000))AS "Product Description",

and tried variations of vlbf such as chr(10) chr(13) or \r\n and none of them worked.

I can't put them in single quotes as SQL will look for the actual occurence in the string rather than what it stands for.

I get the error column vlbf is unknown or chr(10) is an unknown function...|||sorted it now.... used this in the end:

REPLACE(CAST(m.Description AS varChar(1000)),char(13) + char(10),' ') AS "Product Description",

Thanks for the help

Removing Characters in Field grouping

Hello,

I am using the following expression to strip the last 11 characters in a field group and it returns an error saying that Len cannot use a negative number. It must be 0 or greater. Is there a better alternative?

Code Snippet

left(Fields!TestName.Value,len(Fields!TestName.Value)-11))

you can use Rtrim|||

Are you always removing 11 character regardless of the field length or do you simply want to restrict it to a certain size?

For example, the following code restricts the length to 5 characters:

=iif(len(rtrim(Fields!TestDATA.Value)) > 5, mid(rtrim(Fields!TestDATA.Value),1,5), rtrim(Fields!TESTDATA.Value))

Removing all between [and ]

Hi All,
Does anyone know of a way of removing everything between, and including two
given characters in string using TSQL.
e.g. If my result set returns 'Sample Text [12345]' where 12345 is unknown
text, how can I make this 'Sample Text'
I know this is something perhaps best done at application level, but in this
case I need to do this in the data, before it reaches the app.
Thanks!
Simon.lookup charindex() and substring() in bol
Simon Harris wrote:
> Hi All,
> Does anyone know of a way of removing everything between, and including tw
o
> given characters in string using TSQL.
> e.g. If my result set returns 'Sample Text [12345]' where 12345 is unknown
> text, how can I make this 'Sample Text'
> I know this is something perhaps best done at application level, but in th
is
> case I need to do this in the data, before it reaches the app.
> Thanks!
> Simon.
>|||If you are using SQL Server 2005, you could do this with
a .NET UDF and Regular Expressions:
http://www.eggheadcafe.com/articles...5_clr_regex.asp
Robbe Morris - 2004/2005 Microsoft MVP C#
http://www.eggheadcafe.com/forums/merit.asp
"Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
news:%23HkAACQAGHA.312@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> Does anyone know of a way of removing everything between, and including
> two given characters in string using TSQL.
> e.g. If my result set returns 'Sample Text [12345]' where 12345 is unknown
> text, how can I make this 'Sample Text'
> I know this is something perhaps best done at application level, but in
> this case I need to do this in the data, before it reaches the app.
> Thanks!
> Simon.
>|||hi Simon,
Try this out
select substring('Sample Text[12345]',1,charindex('[','Sample
Text[12345]',1)-1) as Result
"Simon Harris" wrote:

> Hi All,
> Does anyone know of a way of removing everything between, and including tw
o
> given characters in string using TSQL.
> e.g. If my result set returns 'Sample Text [12345]' where 12345 is unknown
> text, how can I make this 'Sample Text'
> I know this is something perhaps best done at application level, but in th
is
> case I need to do this in the data, before it reaches the app.
> Thanks!
> Simon.
>
>|||Hi Simon ,
What Manish asked will work but fails for data without any '['
Try This
Select Substring('sample text [12345]', 1, Case When
CharIndex('[','sample text [12345]') > 0 Then CharIndex('[','sample
text [12345]') - 1 Else Len('sample text [12345]') End )
With Warm regards
Jatinder Singh|||Jatinder, your timing could not have been better...I've been trying to work
out what was wrong!! :)
Thanks!!...and to the other guys that replied.
Simon.
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1134646198.240311.227770@.g44g2000cwa.googlegroups.com...
> Hi Simon ,
> What Manish asked will work but fails for data without any '['
> Try This
> Select Substring('sample text [12345]', 1, Case When
> CharIndex('[','sample text [12345]') > 0 Then CharIndex('[','sample
> text [12345]') - 1 Else Len('sample text [12345]') End )
> With Warm regards
> Jatinder Singh
>