ascii - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

ascii

Author

Topic

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-03-16 : 20:05:54

i found this
SET TEXTSIZE 0
SET NOCOUNT ON
-- Create the variables for the current character string position
-- and for the character string.
DECLARE @position int, @string char(15)
-- Initialize the variables.
SET @position = 1
SET @string = 'Du monde entier'
WHILE @position <= DATALENGTH(@string)
BEGIN
SELECT ASCII(SUBSTRING(@string, @position, 1)),
CHAR(ASCII(SUBSTRING(@string, @position, 1)))
SET @position = @position + 1
END
SET NOCOUNT OFF
GO

Is there a way to read each column in my table and check what it has in it

Im having trouble exporting some rows of data

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-17 : 02:46:20

What troubles are you experiencing?

Peter Larsson
Helsingborg, Sweden

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-03-17 : 06:52:23

When using BCP to export data it misses part of the table that i have data in

So i was wondering if i can check data in SQL to see if any non ascii codes

The rows are about 65036 120 mg database

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-03-17 : 10:39:26

The problem is exporting table from SQL.
using bcp some of the records are not exporting.
I put rowid on the table and opened in access and all records are exported...

I assuming some carriage return is missing on the records perhaps

Working on command to display this...

DECLARE @position int, @string char(100), @ASCII_CONCAT INT,
@ASCII_CHAR CHAR,
@ASCII_CONCAT_DISPLAY NVARCHAR(100),
@ASCII_CHAR_DISPLAY NVARCHAR(100)
-- Initialize the variables.
SET @position = 1
SET @string = '"00101001"'
SET @ASCII_CONCAT = ''
SET @ASCII_CONCAT_DISPLAY = ''
SET @ASCII_CHAR_DISPLAY = ''

WHILE @position <= LEN(@STRING)
BEGIN
SET @ASCII_CONCAT = ASCII(SUBSTRING(@string, @position, 1))
SET @ASCII_CHAR = CHAR(ASCII(SUBSTRING(@string, @position, 1)))
--PRINT @ASCII_CHAR
--PRINT @ASCII_CONCAT
SET @position = @position + 1
Set @ASCII_CONCAT_DISPLAY = @ASCII_CONCAT_DISPLAY + @ASCII_CONCAT
SET @ASCII_CHAR_DISPLAY = @ASCII_CHAR + ';' + @ASCII_CHAR_DISPLAY

--SET @ASCII_CHAR_DISPLAY = @ASCII_CHAR + ';' + @ASCII_CHAR_DISPLAY

--PRINT @ASCII_CONCAT_DISPLAY
--PRINT @ASCII_CHAR_DISPLAY
END
PRINT @ASCII_CONCAT_DISPLAY
PRINT @ASCII_CHAR_DISPLAY
SET NOCOUNT OFF
GO

Can't get the ascii for
Set @ASCII_CONCAT_DISPLAY = @ASCII_CONCAT_DISPLAY + @ASCII_CONCAT
to work it add the values together

so im getting 455 instead of 34;48 etc

I think thats how i going to determine if there is something not correct at the end of the columns ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-17 : 14:10:06

Have a look at this topic! http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79083
It filters out everything you don't want there.

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-17 : 19:21:12

[code]Set @ASCII_CONCAT_DISPLAY = @ASCII_CONCAT_DISPLAY + CONVERT(VARCHAR(3), @ASCII_CONCAT) + ';'[/code]

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-03-18 : 07:16:36

i used the function on the columns i get everything coming out
;34;89;34

Get no additonal characters
So it must be something wrong with the bcp

If i add a rowid int it works the bcp exports.
In sql and access.

Any suggestions to why all i can think of is it knows the start and end of each row.

Any thing else i can check

create Function dbo.udf_GetASCIIValue(@Value varchar(50))

Returns varchar(150)

As

Begin
Declare @CurrPos tinyint,
@ValueLen tinyint,
@OutputValue varchar(150)

Select @CurrPos = 1,
@ValueLen = LEN(@Value),
@OutputValue = ''

WHILE @CurrPos <= @ValueLen
Begin
Set @OutputValue = @OutputValue + ';' + CAST(ASCII(SUBSTRING(@Value, @CurrPos, 1)) as varchar(3))
Set @CurrPos = @CurrPos + 1
End

Return @OutputValue

End

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-18 : 07:26:33

Do you have UNICODE column in the table?

Peter Larsson
Helsingborg, Sweden

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-03-18 : 12:19:45

How do i check for this

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-18 : 12:48:11

You can use Enterprise Manager to do this.
Or run this query

SELECT	*
FROM	INFORMATION_SCHEMA.COLUMNS
WHERE	TABLE_NAME = 'YourTableNameHere'

And look amongthe result your column names.

Peter Larsson
Helsingborg, Sweden

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-03-18 : 14:10:02

I have the table on 2000 but i have it here on 2005 so i could work with it.

I got
SQL_Latin1_General_CP1_CI_AS
character set iso_1
TRACEY dbo ET_CHRG COL1 2 NULL YES varchar 100 100 NULL NULL NULL NULL NULL NULL iso_1 NULL NULL SQL_Latin1_General_CP1_CI_AS NULL NULL NULL

All cols are same as above

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-18 : 14:18:45

Ok, now we can rule that out.

Have a look at this code

-- Prepare invalid lookup data
DECLARE	@Lookup TABLE (chr VARCHAR(1))

INSERT	@Lookup
SELECT	CHAR(Number)
FROM	master..spt_values
WHERE	Name IS NULL
	AND Number BETWEEN 1 AND 31

DECLARE	@Data TABLE (cfg VARCHAR(20))

INSERT	@Data
SELECT	'Peso' UNION ALL
SELECT	'XXX' + CHAR(7) + CHAR(9) UNION ALL
SELECT	'Good data' + CHAR(2)

-- Find invalid data
SELECT DISTINCT	d.*
FROM		@Data AS d
INNER JOIN	@Lookup AS l ON d.cfg LIKE '%' + l.chr + '%'

It finds invalid characters in a string.

Use this part of the code in your environment to find invalid rows.

-- Prepare invalid lookup data
DECLARE	@Lookup TABLE (chr VARCHAR(1))

INSERT	@Lookup
SELECT	CHAR(Number)
FROM	master..spt_values
WHERE	Name IS NULL
	AND Number BETWEEN 1 AND 31

-- Find invalid data
SELECT DISTINCT	d.*
FROM		{YourTableNameHere} AS d
INNER JOIN	@Lookup AS l ON {YourTableNameHere}.{YourColumnNameHere} LIKE '%' + l.chr + '%'

Peter Larsson
Helsingborg, Sweden

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-03-18 : 19:19:13

Just so i understand

I run this on the DATABASE
All of it at once ?

-- Prepare invalid lookup data
DECLARE @Lookup TABLE (chr VARCHAR(1))

INSERT @Lookup
SELECT CHAR(Number)
FROM master..spt_values
WHERE Name IS NULL
AND Number BETWEEN 1 AND 31

DECLARE @Data TABLE (cfg VARCHAR(20))

INSERT @Data
SELECT 'Peso' UNION ALL
SELECT 'XXX' + CHAR(7) + CHAR(9) UNION ALL
SELECT 'Good data' + CHAR(2)

-- Find invalid data
SELECT DISTINCT d.*
FROM @Data AS d
INNER JOIN @Lookup AS l ON d.cfg LIKE '%' + l.chr + '%'
It finds invalid characters in a string.

Use this part of the code in your environment to find invalid rows.
-- Prepare invalid lookup data
DECLARE @Lookup TABLE (chr VARCHAR(1))

INSERT @Lookup
SELECT CHAR(Number)
FROM master..spt_values
WHERE Name IS NULL
AND Number BETWEEN 1 AND 31

-- Find invalid data
SELECT DISTINCT d.*
FROM {YourTableNameHere} AS d
INNER JOIN @Lookup AS l ON {YourTableNameHere}.{YourColumnNameHere} LIKE '%' + l.chr + '%'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources