String manipulation syntax

microsoft.public.sqlserver.server

28 Nov 2007 2:46 PM

Hi

Can someone redirect me to a site/document where i can find good string
manipulation information with syntax and example for doing the follwing:

1. In a column, I have values like (123456789.0000). I want to remove "."
and change values to like (1234567890000).

2. Two columns have values like (aaaaa) and (1234). I want to create a new
column with values like (aaaaa1234).

3. I have a column with valus like (aassdd). I want to update the column
with values (111) and so the new values for column will be (111aassdd).

4. I want to create full text indexing on few columns of the table.

jpr

28 Nov 2007 3:01 PM

SQL Menace

>>1. In a column, I have values like (123456789.0000). I want to remove "."
>>and change values to like (1234567890000).

Is this a varchar column? If yes then update table set column
=replace(column,'.','')

>>2. Two columns have values like (aaaaa) and (1234). I want to create a new
>>column with values like (aaaaa1234).

alter table add NewColumn varchar(49)
update table set NewColumn = olcol1 + convert(varchar(20),oldcol2)

>>3. I have a column with valus like (aassdd). I want to update the column
>>with values (111) and so the new values for column will be (111aassdd).

update table set column ='111' + column

Denis The SQL Menace
http://sqlservercode.blogspot.com
http://sqlblog.com/blogs/denis_gobo/default.aspx

On Nov 28, 9:46 am, db <d***@discussions.microsoft.com> wrote:
Show quote

> Hi
>
> Can someone redirect me to a site/document where i can find good string
> manipulation information with syntax and example for doing the follwing:
>
> 1. In a column, I have values like (123456789.0000). I want to remove "."
> and change values to like (1234567890000).
>
> 2. Two columns have values like (aaaaa) and (1234). I want to create a new
> column with values like (aaaaa1234).
>
> 3. I have a column with valus like (aassdd). I want to update the column
> with values (111) and so the new values for column will be (111aassdd).
>
> 4. I want to create full text indexing on few columns of the table.
>
> jpr

28 Nov 2007 3:04 PM

Russell Fields

For SQL Server string functions, start here:
http://technet.microsoft.com/en-us/library/ms181984.aspx

Also, the + is used to concatenate strings. So, '123'+'abcd' = '123abcd'.

Be sure that you are always working with strings, since 123 + 'abcd' will
raise an error:
Conversion failed when converting the varchar value 'abcd' to data type int.

Why? Because 123 is a number and it tried to mathamatically add 123 to
abcd, which is impossible, of course.

Read about Data Type Precedence in:
http://technet.microsoft.com/en-us/library/ms190309.aspx

RLF

Show quote

"db" <d*@discussions.microsoft.com> wrote in message
news:386789AE-7CB9-4E85-957B-E782DB649B78@microsoft.com...
> Hi
>
> Can someone redirect me to a site/document where i can find good string
> manipulation information with syntax and example for doing the follwing:
>
> 1. In a column, I have values like (123456789.0000). I want to remove "."
> and change values to like (1234567890000).
>
> 2. Two columns have values like (aaaaa) and (1234). I want to create a new
> column with values like (aaaaa1234).
>
> 3. I have a column with valus like (aassdd). I want to update the column
> with values (111) and so the new values for column will be (111aassdd).
>
> 4. I want to create full text indexing on few columns of the table.
>
> jpr

28 Nov 2007 8:06 PM

I have a fullname column that has both first name and last name seperated by
"," example
(firstname, lastname) or (firstname, middlename lastname).

How do i create two columns with (first name) and (middlename lastname) out
of one column full_name.

Show quote

"Russell Fields" wrote:

> For SQL Server string functions, start here:
> http://technet.microsoft.com/en-us/library/ms181984.aspx
>
> Also, the + is used to concatenate strings. So, '123'+'abcd' = '123abcd'.
>
> Be sure that you are always working with strings, since 123 + 'abcd' will
> raise an error:
> Conversion failed when converting the varchar value 'abcd' to data type int.
>
> Why? Because 123 is a number and it tried to mathamatically add 123 to
> abcd, which is impossible, of course.
>
> Read about Data Type Precedence in:
> http://technet.microsoft.com/en-us/library/ms190309.aspx
>
> RLF
>
> "db" <d*@discussions.microsoft.com> wrote in message
> news:386789AE-7CB9-4E85-957B-E782DB649B78@microsoft.com...
> > Hi
> >
> > Can someone redirect me to a site/document where i can find good string
> > manipulation information with syntax and example for doing the follwing:
> >
> > 1. In a column, I have values like (123456789.0000). I want to remove "."
> > and change values to like (1234567890000).
> >
> > 2. Two columns have values like (aaaaa) and (1234). I want to create a new
> > column with values like (aaaaa1234).
> >
> > 3. I have a column with valus like (aassdd). I want to update the column
> > with values (111) and so the new values for column will be (111aassdd).
> >
> > 4. I want to create full text indexing on few columns of the table.
> >
> > jpr
>
>
>

28 Nov 2007 9:20 PM

Russell Fields

db,

Here is an answer:

select substring(fullname,1,charindex(',',fullname)-1) AS lastname,
ltrim(substring(fullname,charindex(',',fullname) + 1,100)) as
firstmiddlename
from people
where fullname like '%,%'

This only does minor error handling such as making sure that there is a
comma in the fullname.

RLF

Show quote

"db" <d*@discussions.microsoft.com> wrote in message
news:C2DCB8A8-7634-4A5A-A90A-50617922B2B2@microsoft.com...
>I have a fullname column that has both first name and last name seperated
>by
> "," example
> (firstname, lastname) or (firstname, middlename lastname).
>
> How do i create two columns with (first name) and (middlename lastname)
> out
> of one column full_name.
>
>
>
> "Russell Fields" wrote:
>
>> For SQL Server string functions, start here:
>> http://technet.microsoft.com/en-us/library/ms181984.aspx
>>
>> Also, the + is used to concatenate strings. So, '123'+'abcd' =
>> '123abcd'.
>>
>> Be sure that you are always working with strings, since 123 + 'abcd' will
>> raise an error:
>> Conversion failed when converting the varchar value 'abcd' to data type
>> int.
>>
>> Why? Because 123 is a number and it tried to mathamatically add 123 to
>> abcd, which is impossible, of course.
>>
>> Read about Data Type Precedence in:
>> http://technet.microsoft.com/en-us/library/ms190309.aspx
>>
>> RLF
>>
>> "db" <d*@discussions.microsoft.com> wrote in message
>> news:386789AE-7CB9-4E85-957B-E782DB649B78@microsoft.com...
>> > Hi
>> >
>> > Can someone redirect me to a site/document where i can find good string
>> > manipulation information with syntax and example for doing the
>> > follwing:
>> >
>> > 1. In a column, I have values like (123456789.0000). I want to remove
>> > "."
>> > and change values to like (1234567890000).
>> >
>> > 2. Two columns have values like (aaaaa) and (1234). I want to create a
>> > new
>> > column with values like (aaaaa1234).
>> >
>> > 3. I have a column with valus like (aassdd). I want to update the
>> > column
>> > with values (111) and so the new values for column will be (111aassdd).
>> >
>> > 4. I want to create full text indexing on few columns of the table.
>> >
>> > jpr
>>
>>
>>

29 Nov 2007 11:02 AM

Madhivanan

On Nov 29, 1:06 am, db <d***@discussions.microsoft.com> wrote:
Show quote

> I have a fullname column that has both first name and last name seperated by
> "," example
> (firstname, lastname) or (firstname, middlename lastname).
>
> How do i create two columns with (first name) and (middlename lastname) out
> of one column full_name.
>
>
>
> "Russell Fields" wrote:
> > For SQL Server string functions, start here:
> >http://technet.microsoft.com/en-us/library/ms181984.aspx
>
> > Also, the + is used to concatenate strings. So, '123'+'abcd' = '123abcd'.
>
> > Be sure that you are always working with strings, since 123 + 'abcd' will
> > raise an error:
> > Conversion failed when converting the varchar value 'abcd' to data type int.
>
> > Why? Because 123 is a number and it tried to mathamatically add 123 to
> > abcd, which is impossible, of course.
>
> > Read about Data Type Precedence in:
> >http://technet.microsoft.com/en-us/library/ms190309.aspx
>
> > RLF
>
> > "db" <d***@discussions.microsoft.com> wrote in message
> >news:386789AE-7CB9-4E85-957B-E782DB649B78@microsoft.com...
> > > Hi
>
> > > Can someone redirect me to a site/document where i can find good string
> > > manipulation information with syntax and example for doing the follwing:
>
> > > 1. In a column, I have values like (123456789.0000). I want to remove "."
> > > and change values to like (1234567890000).
>
> > > 2. Two columns have values like (aaaaa) and (1234). I want to create a new
> > > column with values like (aaaaa1234).
>
> > > 3. I have a column with valus like (aassdd). I want to update the column
> > > with values (111) and so the new values for column will be (111aassdd).
>
> > > 4. I want to create full text indexing on few columns of the table.
>
> > > jpr- Hide quoted text -
>
> - Show quoted text -

or read about parsename function in sql server help file

29 Nov 2007 6:09 PM

One more:
I have to concatenate address fields. Concatenation works fine for all rows
except those where Street1 which is the first field is "NULL".

Update tablename set FullAddress = street1 + ' ' + street2 + ' ' + city + '
' + prov_state_code + ' ' + country_code + ' ' + postal_zip

JPR

Show quote

"Madhivanan" wrote:

> On Nov 29, 1:06 am, db <d***@discussions.microsoft.com> wrote:
> > I have a fullname column that has both first name and last name seperated by
> > "," example
> > (firstname, lastname) or (firstname, middlename lastname).
> >
> > How do i create two columns with (first name) and (middlename lastname) out
> > of one column full_name.
> >
> >
> >
> > "Russell Fields" wrote:
> > > For SQL Server string functions, start here:
> > >http://technet.microsoft.com/en-us/library/ms181984.aspx
> >
> > > Also, the + is used to concatenate strings. So, '123'+'abcd' = '123abcd'.
> >
> > > Be sure that you are always working with strings, since 123 + 'abcd' will
> > > raise an error:
> > > Conversion failed when converting the varchar value 'abcd' to data type int.
> >
> > > Why? Because 123 is a number and it tried to mathamatically add 123 to
> > > abcd, which is impossible, of course.
> >
> > > Read about Data Type Precedence in:
> > >http://technet.microsoft.com/en-us/library/ms190309.aspx
> >
> > > RLF
> >
> > > "db" <d***@discussions.microsoft.com> wrote in message
> > >news:386789AE-7CB9-4E85-957B-E782DB649B78@microsoft.com...
> > > > Hi
> >
> > > > Can someone redirect me to a site/document where i can find good string
> > > > manipulation information with syntax and example for doing the follwing:
> >
> > > > 1. In a column, I have values like (123456789.0000). I want to remove "."
> > > > and change values to like (1234567890000).
> >
> > > > 2. Two columns have values like (aaaaa) and (1234). I want to create a new
> > > > column with values like (aaaaa1234).
> >
> > > > 3. I have a column with valus like (aassdd). I want to update the column
> > > > with values (111) and so the new values for column will be (111aassdd).
> >
> > > > 4. I want to create full text indexing on few columns of the table.
> >
> > > > jpr- Hide quoted text -
> >
> > - Show quoted text -
>
>
> or read about parsename function in sql server help file
>

29 Nov 2007 6:20 PM

Russell Fields

JPR,

Read about COALESCE (or the proprietary ISNULL) to see how to handle turning
NULLs into something else.

RLF

Show quote

"db" <d*@discussions.microsoft.com> wrote in message
news:E08E6AB0-8E52-46F5-A1D4-8845C9524DEA@microsoft.com...
> One more:
> I have to concatenate address fields. Concatenation works fine for all
> rows
> except those where Street1 which is the first field is "NULL".
>
> Update tablename set FullAddress = street1 + ' ' + street2 + ' ' + city +
> '
> ' + prov_state_code + ' ' + country_code + ' ' + postal_zip
>
>
>
>
> JPR
>
>
> "Madhivanan" wrote:
>
>> On Nov 29, 1:06 am, db <d***@discussions.microsoft.com> wrote:
>> > I have a fullname column that has both first name and last name
>> > seperated by
>> > "," example
>> > (firstname, lastname) or (firstname, middlename lastname).
>> >
>> > How do i create two columns with (first name) and (middlename lastname)
>> > out
>> > of one column full_name.
>> >
>> >
>> >
>> > "Russell Fields" wrote:
>> > > For SQL Server string functions, start here:
>> > >http://technet.microsoft.com/en-us/library/ms181984.aspx
>> >
>> > > Also, the + is used to concatenate strings. So, '123'+'abcd' =
>> > > '123abcd'.
>> >
>> > > Be sure that you are always working with strings, since 123 + 'abcd'
>> > > will
>> > > raise an error:
>> > > Conversion failed when converting the varchar value 'abcd' to data
>> > > type int.
>> >
>> > > Why? Because 123 is a number and it tried to mathamatically add 123
>> > > to
>> > > abcd, which is impossible, of course.
>> >
>> > > Read about Data Type Precedence in:
>> > >http://technet.microsoft.com/en-us/library/ms190309.aspx
>> >
>> > > RLF
>> >
>> > > "db" <d***@discussions.microsoft.com> wrote in message
>> > >news:386789AE-7CB9-4E85-957B-E782DB649B78@microsoft.com...
>> > > > Hi
>> >
>> > > > Can someone redirect me to a site/document where i can find good
>> > > > string
>> > > > manipulation information with syntax and example for doing the
>> > > > follwing:
>> >
>> > > > 1. In a column, I have values like (123456789.0000). I want to
>> > > > remove "."
>> > > > and change values to like (1234567890000).
>> >
>> > > > 2. Two columns have values like (aaaaa) and (1234). I want to
>> > > > create a new
>> > > > column with values like (aaaaa1234).
>> >
>> > > > 3. I have a column with valus like (aassdd). I want to update the
>> > > > column
>> > > > with values (111) and so the new values for column will be
>> > > > (111aassdd).
>> >
>> > > > 4. I want to create full text indexing on few columns of the table.
>> >
>> > > > jpr- Hide quoted text -
>> >
>> > - Show quoted text -
>>
>>
>> or read about parsename function in sql server help file
>>